Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
Excel2003 ...
I need to determine fractional MONTHS between 2 Dates to 1 decimal place (17.3 months). DATEDIF is returning whole number of months which I cannot seem to format to 1 decimal place. How can you help me? ... Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
Fractional months is a difficult concept to grasp when you consider that a
month is not a uniform unit of measure. A month can have from 28 to 31 days. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... I need to determine fractional MONTHS between 2 Dates to 1 decimal place (17.3 months). DATEDIF is returning whole number of months which I cannot seem to format to 1 decimal place. How can you help me? ... Thanks ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
You can use a formula like
=DATEDIF(A1,B1,"m")+(B1-DATE(YEAR(B1),MONTH(B1),1))/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)) where the start date is in A1 and the end date is in B1. The integer portion of the result is the number of whole months between the two dates. The fractional portion is the day of the end date divided by the number of days in the end month. The same day of month for different months will return different values, since a month can have anywhere from 28 to 31 days. The whole idea of a "fraction of a month" is a bit slippery and open to interpretation. For example, if the end date is the 20th of the month, the fractional portion will be 0.61 if the month has 31 days, 0.68 if the month has 28 days, and 0.63 if the month has 30 days. Take you pick as to which is the "correct" answer. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 18 Mar 2009 13:36:52 -0700, Ken wrote: Excel2003 ... I need to determine fractional MONTHS between 2 Dates to 1 decimal place (17.3 months). DATEDIF is returning whole number of months which I cannot seem to format to 1 decimal place. How can you help me? ... Thanks ... Kha |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
I need to determine fractional MONTHS between 2 Dates to 1 decimal
place (17.3 months). Fractional months is a difficult concept to grasp when you consider that a month is not a uniform unit of measure. A month can have from 28 to 31 days. True, but on the average there are 365.25/12 days in a month. If that's close enough for the OP, then something like =(A2-A1)/(365.25/12) might satisfy the need. Format the result as a number. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
Yes ... (365.25/12) ... is close enough ... Thanks ... Kha
"MyVeryOwnSelf" wrote: I need to determine fractional MONTHS between 2 Dates to 1 decimal place (17.3 months). Fractional months is a difficult concept to grasp when you consider that a month is not a uniform unit of measure. A month can have from 28 to 31 days. True, but on the average there are 365.25/12 days in a month. If that's close enough for the OP, then something like =(A2-A1)/(365.25/12) might satisfy the need. Format the result as a number. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
Chip ... (Good morning)
For my post (365.25/12) is close enough ... However, I am working to understand your solution as well for potential future application. Thank you for supporting these boards & the many solutions you provide ... Kha "Chip Pearson" wrote: You can use a formula like =DATEDIF(A1,B1,"m")+(B1-DATE(YEAR(B1),MONTH(B1),1))/DAY(DATE(YEAR(B1),MONTH(B1)+1,0)) where the start date is in A1 and the end date is in B1. The integer portion of the result is the number of whole months between the two dates. The fractional portion is the day of the end date divided by the number of days in the end month. The same day of month for different months will return different values, since a month can have anywhere from 28 to 31 days. The whole idea of a "fraction of a month" is a bit slippery and open to interpretation. For example, if the end date is the 20th of the month, the fractional portion will be 0.61 if the month has 31 days, 0.68 if the month has 28 days, and 0.63 if the month has 30 days. Take you pick as to which is the "correct" answer. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 18 Mar 2009 13:36:52 -0700, Ken wrote: Excel2003 ... I need to determine fractional MONTHS between 2 Dates to 1 decimal place (17.3 months). DATEDIF is returning whole number of months which I cannot seem to format to 1 decimal place. How can you help me? ... Thanks ... Kha |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
Hello,
I suggest to use =DATEDIF(A1,B1,"m")+WENN(DAY(B1)=DAY(A1),(DAY(B1)-DAY(A1))/(DAY(DATUM (YEAR(B1),MONTH(B1)+1,0))),(DAY(DATUM(YEAR(A1),MON TH(A1)+1,0))-DAY (A1))/(DAY(DATUM(YEAR(A1),MONTH(A1)+1,0)))+DAY(B1)/(DAY(DATUM(YEAR (B1),MONTH(B1)+1,0)))) which is in line with DATEDIF(A1,B1,"d") calculation conventions, for example. Regards, Bernd |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATEDIF?
This is the correct way to calculate it, I believe:
http://www.sulprobil.com/html/monthly_fractions.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Datedif() | Excel Discussion (Misc queries) | |||
DATEDIF | Excel Worksheet Functions | |||
DateDif ? | Excel Worksheet Functions | |||
=DATEDIF | Excel Worksheet Functions | |||
DateDif Average? Damn DateDif | Excel Worksheet Functions |