TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
MonthsBetween([END],[START])
The months between formula calculates # months between dates when crossing into next month.
I.e.
2021-02-16 --> 2021-03-16 = 1 month
but....so does
2021-02-16 --> 2021-03-01 = 1 month....even though it's 13 days

I'd like the same function excel uses in DATEDIF([START],[END],"m") where:
2021-02-16 --> 2021-03-16 = 1month
2021-02-16 --> 2021-04-01 = 1month
2021-02-16 --> 2021-03-15 = 1month
2021-02-16 --> 2021-04-16 = 2month
2021-02-16 --> 2021-04-26 = 2month
2021-02-16 --> 2021-05-05 = 2month
2021-02-16 --> 2021-05-16 = 3month

I've tried using Mod() and Rem() but to be 100% honest, in this case my ability does not meet my ambition 😉
ID
1418
Category
TeamDesk
Author

basenine
Date Created
2/26/2021 12:05:50 AM
Date Updated
3/7/2021 12:54:56 AM
Status
Implemented
Score
20
Promoted By
Patricio Bustosbasenine
Comments
Kirill Bondar  Staff  2/26/2021 6:27:09 AM
MonthsBetween(end, start) counts the number of "1st of the month" between two dates. BTW, same behavior is exposed by QuartersBetween() and YearsBetween().

To match DATEDIF behavior you simply need to subtract 1 from result if ends date's day of the month is less then start date's day of the month.

MonthsBetween([End], [Start]) - If(Day([End]) < Day([Start]), 1)
basenine 2/26/2021 2:29:48 PM
Beautifully simple, Kirill. That’s why you get paid the big bucks 😉

Thank you 👍
basenine 2/26/2021 2:35:49 PM
It’s also blaring obvious, too 🙄

Kirill Bondar  Staff  3/5/2021 6:34:38 AM
DATEDIF function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions.

DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation

(c) Internet
basenine 3/7/2021 12:54:56 AM
I know how DATEDIF feels
😜
Feedback
 
Back to Search Results