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
Datedif_____________________
Try this..
=DATEDIF(A1,B1,"m")+1
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
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.
Best Regards,
Kaper
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 solutionif 4 use datediff
Assumed your Start Dates in A1 and End Date in B1:
=ABS((YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1))+1
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, thanksOriginally Posted by Kaper
Brilliant. I'll be sure to try some of these suggestions tomorrow. Thanks you all, I'll let you know how I get on.
You can get the same result in all cases using a modified DATEDIF, i.e.
=DATEDIF(A1-DAY(A1)+1,B1,"m")+1
Audere est facere
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
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
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
I apologise. It does work. Thanks very much everybody for your help.
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.
For F2:
(sum is not needed here)Formula:Please Login or Register to view this content.
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.
"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."
Last edited by jdrans; 08-30-2019 at 01:10 PM.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks