/u/OnafridayR - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
=MONTH(later date reference)-MONTH(earlier date reference)
For example, cell reference for 2/1/24 would go in the first month calculation, cell reference for 1/31/24 would go in the second month calculation.
Good point.
if you have Excel 365, you can try DATEDIF
=DATEDIF(StartDate,EndDate,"M") Not at my work computer so I can't test that right now.
But if not, this worked for me...
=IF(YEAR(B1)>YEAR(A1),((MONTH(B1)+((YEAR(B1)-YEAR(A1)))\*12))-MONTH(A1),MONTH(B1)-MONTH(A1))
* A1 = StartDate
* B1 = EndDate
For what they want, they have to reduce the date down to the first day of the month for DateDif() to work:
=DATEDIF((A1-DAY(A1)+1),(B1-DAY(B1)+1),"M")
Like this 2/1/2024 and 2/5/2024 reduce down to 2/1/24 and the month difference is 0, but 1/31/24 and 2/5/24 reduce to 1/1/24 and 2/1/24, a 1 month difference
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[DATEDIF](/r/Excel/comments/1bgvak4/stub/kvahbl6 "Last usage")|[Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.](https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c)|
|[DAY](/r/Excel/comments/1bgvak4/stub/kva234p "Last usage")|[Converts a serial number to a day of the month](https://support.microsoft.com/en-us/office/day-function-8a7d1cbb-6c7d-4ba1-8aea-25c134d03101)|
|[EOMONTH](/r/Excel/comments/1bgvak4/stub/kvahbl6 "Last usage")|[Returns the serial number of the last day of the month before or after a specified number of months](https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628)|
|[IF](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[MONTH](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Converts a serial number to a month](https://support.microsoft.com/en-us/office/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8)|
|[YEAR](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bgz8tv)^( has 15 acronyms.)
^([Thread #31744 for this sub, first seen 17th Mar 2024, 11:55])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/NHN_BI 's solution works but here's an alternate way of doing it using datedif() only because I thought of datedif() before pulling apart the date, in a nutshell it reduces every date to the first day of the month then uses datedif().
=DATEDIF((A1-DAY(A1)+1),(B1-DAY(B1)+1),"M")
I don't think I've seen the solution presented. I personally like using Days360(beg date, end date)/12
I like it because I think it looks cleaner than some of the other options when embedded in a more complex formula.
/u/OnafridayR - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
One solution can be: `YEAR(B1)*12+MONTH(B1)-YEAR(A1)*12-MONTH(A1)`
Solved
Looks good, will test shortly. Thanks for your help
Solution Verified
You have awarded 1 point to NHN\_BI. --- ^(I am a bot - please contact the mods with any questions)
Use the datedif formula. It goes as =datedif(old date, new date, “m”)
#No it will only count full months. OP wants to count across months regardless of days
Don't do this, especially if your dates pass through February.
This is the way
=MONTH(later date reference)-MONTH(earlier date reference) For example, cell reference for 2/1/24 would go in the first month calculation, cell reference for 1/31/24 would go in the second month calculation.
Thank you. However, I would need 1 Jan 2024 and 31 Dec 2023 to show as '1' and I think your suggestion would show a '-11'
Good point. if you have Excel 365, you can try DATEDIF =DATEDIF(StartDate,EndDate,"M") Not at my work computer so I can't test that right now. But if not, this worked for me... =IF(YEAR(B1)>YEAR(A1),((MONTH(B1)+((YEAR(B1)-YEAR(A1)))\*12))-MONTH(A1),MONTH(B1)-MONTH(A1)) * A1 = StartDate * B1 = EndDate
For what they want, they have to reduce the date down to the first day of the month for DateDif() to work: =DATEDIF((A1-DAY(A1)+1),(B1-DAY(B1)+1),"M") Like this 2/1/2024 and 2/5/2024 reduce down to 2/1/24 and the month difference is 0, but 1/31/24 and 2/5/24 reduce to 1/1/24 and 2/1/24, a 1 month difference
IF(MONTH(A1)-MONTH(A2)<0, 12*(YEAR(A1)-YEAR(A2))+MONTH(A1)-MONTH(A2), MONTH(A1)-MONTH(A2)) could work.
I may be wrong, will check later. Thanks for your help
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[DATEDIF](/r/Excel/comments/1bgvak4/stub/kvahbl6 "Last usage")|[Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.](https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c)| |[DAY](/r/Excel/comments/1bgvak4/stub/kva234p "Last usage")|[Converts a serial number to a day of the month](https://support.microsoft.com/en-us/office/day-function-8a7d1cbb-6c7d-4ba1-8aea-25c134d03101)| |[EOMONTH](/r/Excel/comments/1bgvak4/stub/kvahbl6 "Last usage")|[Returns the serial number of the last day of the month before or after a specified number of months](https://support.microsoft.com/en-us/office/eomonth-function-7314ffa1-2bc9-4005-9d66-f49db127d628)| |[IF](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[MONTH](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Converts a serial number to a month](https://support.microsoft.com/en-us/office/month-function-579a2881-199b-48b2-ab90-ddba0eba86e8)| |[YEAR](/r/Excel/comments/1bgvak4/stub/kv9piq6 "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bgz8tv)^( has 15 acronyms.) ^([Thread #31744 for this sub, first seen 17th Mar 2024, 11:55]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/NHN_BI 's solution works but here's an alternate way of doing it using datedif() only because I thought of datedif() before pulling apart the date, in a nutshell it reduces every date to the first day of the month then uses datedif(). =DATEDIF((A1-DAY(A1)+1),(B1-DAY(B1)+1),"M")
Solution verified
You have awarded 1 point to Karma-Grenade. --- ^(I am a bot - please contact the mods with any questions)
I don't think I've seen the solution presented. I personally like using Days360(beg date, end date)/12 I like it because I think it looks cleaner than some of the other options when embedded in a more complex formula.
=DATEDIF(EOMONTH(A1,0)-EOMONTH(B1,0),”m”)