Months in excel

[Bassmansam]

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

 

[montymint]

DATEDIF my dude

Calculate the difference between two dates - Microsoft Support

How to calculate the number of days, months, or years between two dates using the DATEIF function in Excel.

support.microsoft.com

 

[Bassmansam]

Ah yes i have used that before. I read that its not officially supported any more even though it does work though which is weird.

 

[james.miller]

use DAYS(). DATEDIF() can return the wrong number of days under certain circumstances.

 

[Bassmansam]

use DAYS(). DATEDIF() can return the wrong number of days under certain circumstances.

if you use days, how would you get a month figure, divide by 30?

Also, have you got ay examples where datedif would give he wrong days please?

Thanks for your help

 

[james.miller]

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:

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.