counting months between dates and returning a decimal

I need to calculate the number of months between two dates 1/1/07 and the current date on a paystub. I need the answer as a decimal so that I can YTD income properly. What is the easiest way to do this?

1/1/07
6/15/07

should return a value of 5.5

Currently I am using =DATEDIF(B2,B10,"m") but it rounds the months down.

To do this you need to do some estmation of the average month length. Assuming that's 30.5 days try

=(B10-B2)/30.5

format cell as number

Originally Posted by mudraker

It gives a method of calculating last day of month to give full decimal answer

hello mudraker, are you referring to this formula?

=DATEDIF(A1,B1,"m")+(DATEDIF(A1,B1,"md")/DAY(EOMONTH(B1,0)))

You can get some odd results with DATEDIF formulas. If A1 contains the date 30th November 2006 then look at the results obtained by putting the following successive dates in B1

26/02/2007 2.96
27/02/2007 3.00
28/02/2007 3.04
01/03/2007 2.97
02/03/2007 3.00
03/03/2007 3.03
04/03/2007 3.06

[note dates in dd/mm/yyyy format]

The below formula is ugly but it works!!!!.... all in one cell, no macro needed! It takes in to account each month's days regardless if it is has 30, 31, or in the instance of February 28 days. It also takes into account leap year where February has 29 days.

A1 = Start Date
B2 = Last Date

=(IF(MONTH(A1)=MONTH(B1),(YEAR(B1)-YEAR(A1))*11,((YEAR(B1)-YEAR(A1))*12)-12+(12-MONTH(A1))+MONTH(B1)-1))+(EOMONTH(A1,0)-A1+1)/DAY(EOMONTH(A1,0))+(1-(EOMONTH(B1,0)-B1)/DAY(EOMONTH(B1,0)))

Try it! It works!

Hello Claudster,

Great formula! It works fantastically when the gap between the two dates is over a month. Whereas when it is lower than a month the result is always 1 (month) higher than is should be. I have attempted to correct it but been unsuccessful, could you please help me to correct it? Below shows a small copy paste out of my spreadsheet where you can see the result of 1.87 is exactly one month too much.

Many thanks in advance,
Jake

From: 05 April 2016
To: 30 April 2016
Months: 1.87

Just in case someone else needs this solution I fixed it by using the following formula:

=IF(MONTH(D2)=MONTH(D3),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))))

Where the start date is D2 and the end date is D3.

Hi Jake,

May I know if this is applicable if it's between two dates more than one year?

Originally Posted by learnmoreexcel

Hi Jake,

May I know if this is applicable if it's between two dates more than one year?

Yes it is, I hope it works for you.

Thanks for the previous contributors, much appreciated. You have allowed me to determine solutions for determination of months as a fraction between two dates. Below is my contribution.

E28 is the finish date and
D28 is the start date

The number of months as a fraction is given by the following;

This method evaluates the difference between 2 dates and expresses this difference as fractions of a month. Note: This method is not inclusive as number of days does not include the start date. For example, if we determine the number of days between the 4th July and the 21st July an inclusive method would result in 18 days whereas a difference method would result in 17 days. Hence the fraction of the month would be 17 / 31 = 0.5484. Also as the number of days vary in a month (28 to 31), this method will produce unusual results e.g. if the start date is the 28/08/2014 and the finish date is 28/02/2015 then the calculated number of months is 6.0950 not 6. This is due to August having 31 days and February having 28 days. 0.0950 = 27.5 / 28 - 27.5 / 31.

If you wish to have the time of the dates at midday then the formula is

= (YEAR(E28) -YEAR(D28)) * 12 + MONTH(E28) - MONTH(D28) + (DAY(E28) - 0.5) / DAY((EOMONTH(E28,0))) - (DAY(D28) - 0.5) / DAY((EOMONTH(D28,0)))

If you wish to have the time of the dates at midnight AM then the formula is

= (YEAR(E28) -YEAR(D28)) * 12 + MONTH(E28) - MONTH(D28) + (DAY(E28) - 1.0) / DAY((EOMONTH(E28,0))) - (DAY(D28) - 1.0)/DAY((EOMONTH(D28,0)))

If you wish to have the time of the dates at midnight PM then the formula is

= (YEAR(E28) -YEAR(D28)) *12 + MONTH(E28) - MONTH(D28) + (DAY(E28) - 0) / DAY((EOMONTH(E28,0))) - (DAY(D28) - 0) / DAY((EOMONTH(D28,0)))

Note the variation in result between the above three methods is small.

Explanation
The first two portions of the formula (see below) determine the number of months at the beginning of the two months of concern. This is simply the number of years times 12 and the difference of the number of months. This portion works correctly even if the difference in months is either positive, zero or negative.

(YEAR(E28) -YEAR(D28)) * 12 + MONTH(E28) - MONTH(D28)

The third portion of the formula (see below) determines the period from the beginning of the "finish date month" to the finish date. The "Finish Date" occurs outside the period of the beginnings of the "Start Date" and "Finish Date" months and hence the fraction of the month has to be added. The reason for the -0.5 is as follows; 0.5 represents midday, hence if the Start Date is the fourth day of the month then 3.5 days have passed since the beginning of the month and hence the 0.5 has to be subtracted from the month's day number.

+ (DAY(E28) - 0.5) / DAY((EOMONTH(E28,0))).

The fourth portion of the formula (below) determines the period from the beginning of the "start date month" to the start date. The "Start Date" occurs within the period of the beginnings of the "Start Date" and "Finish Date" months and hence the fraction of the month has to be trimmed. The inclusion of the -0.5 is the same as per above.

- (DAY(D28) - 0.5) / DAY((EOMONTH(D28,0)

Simplified method

A simplified method (see below) is to determine the number of days and convert them into months by multiplying by 12 and dividing by 365.25, the average number of days in a year. This method has an error of the order of 1%.

= DATEDIF(D28,E28,"d") * 12 / 365.25

The same formulas are not working for me. Any thoughts?

Start 8/27/2017
End 6/26/2018
months 10.03

Not sure which formula you are using.
Using justjakehog's formula I get 10.0280.
Using my method I get 9.9952. Justjakehog formula is an inclusive method i.e. includes the start date whereas mine does not. Hence an additional day is approx 1/30 of month or 0.0333 of a month.

Thanks justjakehogan - this worked for me.

Originally Posted by justjakehogan

Just in case someone else needs this solution I fixed it by using the following formula:

=IF(MONTH(D2)=MONTH(D3),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))))

Where the start date is D2 and the end date is D3.

it shows the result '#NUM!' when use [=datedif(B1,A1,"m"]

started date [A1] : 01/09/2017
end date [B1] : 28/09/2017

WHY?

Originally Posted by justjakehogan

Just in case someone else needs this solution I fixed it by using the following formula:

=IF(MONTH(D2)=MONTH(D3),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))))

Where the start date is D2 and the end date is D3.

This method won't work properly. I have tried with several dates and it shows incorrect amount of months.

I made my own algorithm, here it is:
I used Defined Names for easy debugging.
Cell B2 - Start date
Cell B3 - End date

Lease_IntMonths = DATEDIF( B2; B3+1; "m" )
Calculate an integer amount of months between Start and End dates.

NewEndDate = EDATE(B2; Lease_IntMonths )
Add the amount of months to the original Start date and get a new End date.

Lease_DaysInLastMonth = B3+1 - NewEndDate
Calculate how many days are in the last month of the period.

CalendarDaysInLastMonth = DAY( EOMONTH( NewEndDate; 0 ) )
Calculate the amount of calendar days in the last month of the period.

=Lease_IntMonths + IF( Lease_DaysInLastMonth > 0; Lease_DaysInLastMonth / CalendarDaysInLastMonth; 0 )
Calculate the final integer part of the amount of months plus a fraction.

its excellent its really work. But their is one problem
it didnit count date from 1jan to 31 jan as one month it calculate it as 2 month so please check the problem

your reply will be appreciated

Thanks alot....

Originally Posted by justjakehogan

Just in case someone else needs this solution I fixed it by using the following formula:

=IF(MONTH(D2)=MONTH(D3),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))),((YEAR(D3)-YEAR(D2))*12)-12+(12-MONTH(D2))+MONTH(D3)-1+(EOMONTH(D2,0)-D2+1)/DAY(EOMONTH(D2,0))+(1-(EOMONTH(D3,0)-D3)/DAY(EOMONTH(D3,0))))

Where the start date is D2 and the end date is D3.

I came across this thread after developing a similar, albeit, simplified solution. Try this and you should get the same result:

=DATEDIF(EOMONTH(D2,0)+1,EOMONTH(D3,0)+1,"m")+(DAY(D3)/DAY(EOMONTH(D3,0)))-((DAY(D2)-1)/DAY(EOMONTH(D2,0)))