In the attached, I've highlighted the formula in question that's seemingly comparing December to February and treating it as one month. I initially thought it was because of the number of days in Feb, but I've also highlighted in green an issue I noticed in another workbook, wherein the formula *should* return 3 months for April, May, June, but it instead returns the difference between April and June (2). Is there a catch-all solution to this inconsistency? Thanks in advance for the help!
Last edited by Drexl27; 05-03-2024 at 12:56 PM.
I suspect it is because it is calculating full months
See: https://support.microsoft.com/en-us/...d-8b32a451b35cPHP Code:Unit
The type of information that you want returned, where:
Unit
Returns
"Y"
The number of complete years in the period.
"M"
The number of complete months in the period.
"D"
The number of days in the period.
"MD"
The difference between the days in start_date and end_date. The months and years of the dates are ignored.
Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.
"YM"
The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD"
The difference between the days of start_date and end_date. The years of the dates are ignored.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Why should it return 3 monts fro 30th APRIL to 30th JUNE.... 61 days in total???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
@Glenn: it would be this:
31/03/2024 =EOMONTH(F12, -1)
30/06/2024 =EOMONTH(G12, 0)
I think it might only be counting April and May. I don't think it is including the last day. Just a guess.
01/07/2024 =EOMONTH(G12, 0)+1 gives the result = 3.
Last edited by TMS; 04-25-2024 at 05:40 PM.
What I'm seeking is a formula that consistently calculates the number months *including* the start date. So if the start date is 04/30 and current date is 06/30, I want it to count that as 3 months. It works most of the time, but for some reason there are some periods between months that just don't calculate a full month and I can't figure out why.
You probably need:
=DATEDIF(EOMONTH(F11, -1), EOMONTH(A5, 0)+1, "m")
in L11 and, in K12:
=DATEDIF(EOMONTH(F12, -1), EOMONTH(G12, 0)+1, "m")
If I'm not mistaken that adds a day. I'll run that through all my workbooks to see if this is a consistent fix - thank you!
It adds a day to the end of the month. This generates the first day of the following month, thus guaranteeing a full month.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks