Formula to show number of months (1st and last month inclusive) between 2 dates

1. Formula to show number of months (1st and last month inclusive) between 2 dates

Hi,

I've been trying to sort a formula to work out the number of months between 2 dates. The number must include the 1st month and the last. It also must be able to work between years. I'll give some examples

1/5/14 - 1/9/14 = 5 months
1/10/14 - 1/4/15 = 7 months
1/5/15 - 1/4/16 = 12 months

I've come close with some furmula's I've found googling but none seem to work quite right.

Any help will be much appreciated.

Ant

2. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Datedif_____________________

3. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Try this..

=DATEDIF(A1,B1,"m")+1

4. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

If your dates are real excel dates (not just texts) and are in A1 start B1 finish:
Formula:
`Please Login or Register  to view this content.`

5. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

PS. With datediff remember to format your cell as general (exlcel will try to automatically format it as date ) and it will be probably different than you need if dates are not always beginning of month. try:
15/5/14 - 1/9/14 - shall it be still 5 months? if yes - use my solution if 4 use datediff

6. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Assumed your Start Dates in A1 and End Date in B1:

=ABS((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1))+1

7. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Hi,
If indeed "Start Dates in A1 and End Date in B1" then ABS is not needed.
Only when "there are two dates in A1 and B1, one of them is start, and the other is end" ABS would be really needed.

8. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Originally Posted by Kaper
Hi,
If indeed "Start Dates in A1 and End Date in B1" then ABS is not needed.
Only when "there are two dates in A1 and B1, one of them is start, and the other is end" ABS would be really needed.
Yes Im miss that one, no need start date and end date, thanks

9. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Brilliant. I'll be sure to try some of these suggestions tomorrow. Thanks you all, I'll let you know how I get on.

10. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Originally Posted by Kaper
=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)+1
You can get the same result in all cases using a modified DATEDIF, i.e.

=DATEDIF(A1-DAY(A1)+1,B1,"m")+1

11. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

EXAMPLE SHEET - DATES.xlsx

I am struggling to make any of these formulas work. I have attached an example sheet to demonstrate what it is I am trying to achieve.

Thanks all,

Ant

12. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

what doesnt work? daddylonglegs offer gives the results shown in col J
=SUM(S2-(G2*F2)-(R2*F2)) gives #value as col g is text and not a number

13. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

I apologise. It does work. Thanks very much everybody for your help.

14. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

Is there any easy way to make it so nothing shows in any of the cells until data is entered on that line. At the minute it shows #NUM in the F J P & S cells.

15. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

For F2:
Formula:
`Please Login or Register  to view this content.`
(sum is not needed here)
and similar approach to other cells
So for instance J2 (sed my version, but of course you can use whichever you want):
Formula:
`Please Login or Register  to view this content.`

etc.

16. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

"Assumed your Start Dates in A1 and End Date in B1:

=ABS((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1))+1"

5 years later, this answer worked for me, though in my case I didn't want "first month inclusive" so I removed the plus one.
My requirement can best be stated as "I want to know how many 1st of the month's there are between two dates."

17. Re: Formula to show number of months (1st and last month inclusive) between 2 dates

You can do the following which will work:

If in A1 you have the first date and in A2 you have the subsequent date:

=month(A2)-month(A1)+1

eg. If you want to know the number of months inclusive of the starting month for 2/1/2019 and 7/1/2019 then

=month(A1) will result in 2
=month(A2) will result in 7

So you'll get =7-2+1=6

This seemed easiest to me.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1