# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  counting months between dates and returning a decimal

## Mycotopian

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.

----------


## daddylonglegs

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

----------


## mudraker

have a look at
http://www.ozgrid.com/forum/showthread.php?t=38258

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

----------


## daddylonglegs

> 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]

----------


## mudraker

daddylonglegs

Yes I was refering to that formula

I have never used Datedif in a formula. I have used datediff in macros for calculating number of days but never months except in the threads of the links below

http://www.excelforum.com/showthread.php?t=598055
http://www.ozgrid.com/forum/showthread.php?t=67891

----------


## Claudster

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! :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:  :Smilie:

----------


## justjakehogan

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

----------


## 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.

----------


## learnmoreexcel

Hi Jake,

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

----------


## FDibbins

learnmoreexcel, welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## justjakehogan

> 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.

----------


## MrBoris173

> 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!



Awesome work, I am sure I've had a solution to this before, but this was EXACTLY what i was looking for today.  Why reinvent the wheel - Excelforum is an awesome place to look for solutions!   Cheers Claudster!   Happy Easter!

----------


## Johnadowsett

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

----------


## aandrews1122

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

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

----------


## Johnadowsett

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.

----------


## FDibbins

> The same formulas are not working for me. Any thoughts?
> 
> Start	8/27/2017
> End	6/26/2018
> months	 10.03



heres a thought - Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## pshnfry

Thanks justjakehogan - this worked for me.





> 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.

----------


## phalsovannarith

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

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

WHY?

----------


## SotMrus

> 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.

----------


## rwagner88

I just learned about yearfrac. Try: =YEARFRAC(cell_of_start_date,cell_of_end_date)*12

----------


## ALNORAINAHMAD

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....

----------


## FDibbins

> 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....



*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

