Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Example 1
Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April" Your kind support is greatly appreciated. TQ!//nginhong |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hi
=DATEDIF(StartDate,EndDate,"M") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Example 1 Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April" Your kind support is greatly appreciated. TQ!//nginhong |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hello Arvi,
It seems like the formula you provided contains error. BR//nginhong "Arvi Laanemets" wrote: Hi =DATEDIF(StartDate,EndDate,"M") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Example 1 Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April" Your kind support is greatly appreciated. TQ!//nginhong |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hi
What kind of error? I'm afraid you dates aren't really dates at all, but strings. Change the format for some date to general - when the value in cell turns to number, then the entry was a date, otherwise it was not. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Hello Arvi, It seems like the formula you provided contains error. BR//nginhong "Arvi Laanemets" wrote: Hi =DATEDIF(StartDate,EndDate,"M") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Example 1 Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April" Your kind support is greatly appreciated. TQ!//nginhong |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hello Arvi,
Thanks for you reply. Let me know frame the idea of how the excel sheet looks like. I put start date in cell A2 = 12/04/2004 & end date A3 = 13/04/2006 Then use your formula as in cell A4 =DATEDIF((A2),(A3),"M") and press enter, however error message shows:- The formula you typed contains error. - For information about fixing common formula problems, click Help. - To get assistance in entering a function, click OK, then click Function on the Insert menu. - If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (') BR//nginhong "Arvi Laanemets" wrote: Hi What kind of error? I'm afraid you dates aren't really dates at all, but strings. Change the format for some date to general - when the value in cell turns to number, then the entry was a date, otherwise it was not. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Hello Arvi, It seems like the formula you provided contains error. BR//nginhong "Arvi Laanemets" wrote: Hi =DATEDIF(StartDate,EndDate,"M") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "nginhong" wrote in message ... Example 1 Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April" Your kind support is greatly appreciated. TQ!//nginhong |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hi
"nginhong" wrote in message ... Hello Arvi, Thanks for you reply. Let me know frame the idea of how the excel sheet looks like. I put start date in cell A2 = 12/04/2004 & end date A3 = 13/04/2006 Select cells A2:A3, and set cell format to General. When those are really dates, then now you see values 38089 and 38819 Then use your formula as in cell A4 =DATEDIF((A2),(A3),"M") and press enter, You can simplify the formula a bit =DATEDIF(A2,A3,"M") however error message shows:- The formula you typed contains error. - For information about fixing common formula problems, click Help. - To get assistance in entering a function, click OK, then click Function on the Insert menu. - If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (') What is function parameter delimiter for your regional settings? Comma or semicolon? Try: =DATEDIF(A2;A3;"M") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
On Wed, 12 Apr 2006 03:21:01 -0700, nginhong
wrote: =DATEDIF((A2),(A3),"M") Perhaps your country version of excel uses semicolons for delimiters? =DATEDIF((A2);(A3);"M") Also, the parentheses around the cell references are unnecessary: =DATEDIF(A2;A3;"M") --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
How about: =(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2) +ROUNDUP((DAY(A3)-DAY(A2))/31,0) Closer? -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hello John,
It seems like the formula you provided contains error. BR//nginhong "John James" wrote: How about: =(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2) +ROUNDUP((DAY(A3)-DAY(A2))/31,0) Closer? -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hello John,
I managed to find out the error of the formula because the regional setting in my PC is using semicolon for delimiters. I would say you formula is very close to what I am looking forward but the set back a- =(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2)+ROUNDUP((DAY(A3)-DAY(A2))/31;0) 1. Unable to show result as 1 month if start date is 28/02/2006 and end date is 02/03/2006. 2. Unable to show result as 13 months if start date is 28/02/2006 and end date is 02/03/2007. However I really appreciate your help. Thanks & Regards, Ngin Hong "John James" wrote: How about: =(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2) +ROUNDUP((DAY(A3)-DAY(A2))/31,0) Closer? -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hi Ngin Hong, So does this work then? =(YEAR(A3)-YEAR(A2))*12+MONTH(A3)-MONTH(A2)+ROUNDUP(IF(DAY(A3)DAY(A2),DAY(A3)-DAY(A2),0)/31;0) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Hi Ngin Hong, Simplifying it: Just adding this to your original formula should work I think +IF(DAY(A4)DAY(A3),1,0) gives: =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)+IF(DAY(A4)DAY(A3),1,0) nginhong Wrote: =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to calculate "number of months" between two given date?
Thanks John,
It works fine now! BR//nginhong "John James" wrote: Hi Ngin Hong, Simplifying it: Just adding this to your original formula should work I think +IF(DAY(A4)DAY(A3),1,0) gives: =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)+IF(DAY(A4)DAY(A3),1,0) nginhong Wrote: =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=532164 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate age using birth date and current date | Excel Worksheet Functions | |||
Calculate a biweekly date | Excel Discussion (Misc queries) | |||
how to count weeks from date "X" then calculate | Excel Worksheet Functions | |||
calculate payment with first payment due date variable? | Excel Worksheet Functions | |||
calculate the number of days from date received | Excel Worksheet Functions |