Calculating Dates in Terms of Months

[Guest]

I am trying to find a function that will allow me to calculate the difference
between two dates. I want the answer to be shown in months only, therefore I
would need any additional days to be converted to a decimal. I tried to the
datedif function, but it does not recognize that the time span between months
may cross over multiple years.

For example I have the following:
Start Date: 04/15/02
End Date: 06/28/03

The number of whole months in between is 14, however their are also
additional days in between and I am looking to convert those to a decimal.
I know the difference in months

 

[Guest]

datedif does do total months
if you can stand using 30 days for each month for your decimal try

=datedif(date1,date2,"m") +datedif(date1,date2,"md")/30

if you want to be more complex than the 30 days per month approximation, the
equation gets more complicated.

 

[Roger Govier]

Hi Jessica

One way, with start date in A1 and end date in A2
=DATEDIF(A1,A2,"m")&MID(DATEDIF(A1,A2,"md")/DAY(EOMONTH(A2,0)),2,4)

You need to load the addin Analysis ToolPak for this to work
Tools>Addins and check Analysis Toolpak

Regards

Roger Govier

 

[Roger Govier]

Hi Jessica

I forgot to say you need to format the cell with the formula as General.
Also, if you want it as a number and not text, then wrap the equation in
brackets and add 0

=(DATEDIF(A1,A2,"m")&MID(DATEDIF(A1,A2,"md")/DAY(EOMONTH(A2,0)),2,4))+0


Regards

Roger Govier

 

[Ron Rosenfeld]

I am trying to find a function that will allow me to calculate the difference
between two dates. I want the answer to be shown in months only, therefore I
would need any additional days to be converted to a decimal. I tried to the
datedif function, but it does not recognize that the time span between months
may cross over multiple years.

For example I have the following:
Start Date: 04/15/02
End Date: 06/28/03

The number of whole months in between is 14, however their are also
additional days in between and I am looking to convert those to a decimal.
I know the difference in months

What do you mean by a "whole month".

If you mean Calendar month, then there are only 13 "whole months" + 2
fractional months -- in this case 15/30 + 28/30. (I think this is the least
ambiguous method).

If you mean the corresponding day in the End Date month, then you have to
decide how to deal with the issue of what happens if there is no corresponding
day in the End Date month, and also what your denominator will be for the
fractional month (the start month, the end month, 30, 30.416667, etc)

If you use the DATEDIF function, as suggested by another poster, you wind up
with the following:

StartDate EndDate DateDif CalendarMonths
28-Jan-2005 28-Feb-2005 1.00 1.10
29-Jan-2005 28-Feb-2005 1.00 1.06
30-Jan-2005 28-Feb-2005 0.97 1.03
31-Jan-2005 28-Feb-2005 0.93 1.00

There is no "right or wrong"; it's a matter of understanding the results you
obtain. But note that with DateDif, it is possible to obtain the same result
with two different StartDate's.

You also need to decide whether or not you wish to count both the StartDate and
EndDate, or merely subtract the one from the other.


--ron