Months in excel

Soldato
Joined
22 Nov 2007
Posts
4,270
Hi All

Usually when writing a formula and i want to get a month figure, e.g. doing the following subtraction would give around 60 days, then i would divide by 30 , which gives 2. This feels like a dirty way of doing it, isn't there an official way?

10/10/2022 - 10/08/2022
 
No, datedif is fine for months, it's only when using it to calculate days where issues can occur.

from the help text:

Support page

DATEDIF function​

Calculates the number of days, months, or years between two dates.
Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. Please see the known issues section of this article for further details

Known issues​

The "MD" argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month, here is a workaround:
=DATEDIF(D17,E17,md) and result: 5

This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Here's how it does this: First the DATE function creates the date, 5/1/2016. It creates it using the year in cell E17, and the month in cell E17. Then the 1 represents the first day of that month. The result for the DATE function is 5/1/2016. Then, we subtract that from the original end date in cell E17, which is 5/6/2016. 5/6/2016 minus 5/1/2016 is 5 days.
 
Last edited:
Back
Top Bottom