DATEDIF?

[Ken]

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

 

[T. Valko]

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.

 

[Chip Pearson]

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)

 

[MyVeryOwnSelf]

I need to determine fractional MONTHS between 2 Dates to 1 decimal

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.

 

[Ken]

Yes ... (365.25/12) ... is close enough ... Thanks ... Kha

 

[Ken]

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

 

[Bernd P]

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),MONTH(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

 

[Bernd P]

This is the correct way to calculate it, I believe:
http://www.sulprobil.com/html/monthly_fractions.html

Regards,
Bernd