Table of Contents
To convert days to months in Excel, divide the number of days by 30.4, which is the average number of days in a month. This will give you the approximate number of months. To get a more precise answer, use the DATEDIF function, which allows you to specify the exact start and end dates you want to use for the calculation. When using the DATEDIF function, specify “m” as the third argument to indicate you want to calculate the difference in months.
You can use the following formula in Excel to convert days to months:
=(B1-A1)/(365/12)
This formula calculates the number of months between cell B1 (the end date) and cell A1 (the start date).
The following example shows how to use this formula in practice.
Example: Convert Days to Months in Excel
Suppose we have the following list of start and end dates in Excel:
We could use the following formula to calculate the number of days between each start and end date:
B2-A2
We can type this formula into cell C2 and then copy and paste it down to every remaining cell in column C:
Column C shows the difference (in days) between each start and end date.
However, we could instead use the following formula to calculate the difference between each start and end date in terms of months:
=(B2-A2)/(365/12)
We can type this formula into cell C2 and then copy and paste it down to every remaining cell in column C:
Column C shows the number of months (including decimal places) between each start and end date.
- There are 0.5589 months between 1/1/2020 and 1/18/2020.
- There are 2.1041 months between 2/1/2020 and 4/5/2020.
- There are 0.1644 months between 3/1/2020 and 3/6/2020.
And so on.
Note that you could instead use this formula to calculate the number of months between two dates if you’d like to assume that 30 days represents an average month:
=(B2-A2)/30
However, this is not as accurate as using (365/12) as the denominator in the formula.