Whether managing a project or tracking personal milestones, converting days to months in Excel can be useful.
With simple formulas, you can easily transform any given number of days into its equivalent number of months.
This way, you can better understand the duration of a specific event or task, compared to evaluating it solely in days.
In this Excel article, I’m going to show you how to convert days to months in Excel using four different methods.
Method 1 – Dividing the Number of Days by the Average Number of Days Per Month
In Excel, you can convert days to months without using any Excel function.
Dividing the number of days by the average number of days per month would give us the number of months.
The table below is from a debtor analysis. Column B shows how many days have passed from each invoice payment’s due date. In column C, I want the value after the days have been converted to months.
In cell C2, use the below formula to convert the given number of days to months.
=B2/(365/12)
In this formula, we simply divide the number of days in each cell of column B by the average number of days per month.
So we select B2 as the number of days for the formula. To calculate the average number of days per month, divide the average number of days per year (365 days) by the number of months per year (12).
Therefore, we divide the given number of days by 365/12.
Say we wish to calculate how many months have been completed. The formula’s result then needs to be rounded down.
The updated formula is as follows;
=ROUNDDOWN(B2/(365/12),0)
The ROUNDDOWN formula has the syntax ROUNDDOWN(number, num_digits).
For the first argument, we can use the same Excel formula we used to convert days to months. For the second argument, we have used 0 as we don’t want any decimal values in the result.
When we divide 365 by 12, we get 30.42. So we can substitute roughly 30 days for this 365/12. In other words, we can assume that a month has 30 days on average. Our formula will then be =B2/30
The second formula, dividing the number of days by 30, is simpler to use and understand than the first.
Also read: How to Convert Date to Day of Week in Excel?
Method 2 – Using DATEDIF Function to Convert Days to Months in Excel
DATEDIF is one of the functions you can use when you have the number of days, and you want to convert these days into months.
Here is a dataset where I have the number of days in column B, and I want to get the number of months completed by these days in column C.
To convert the given number of days to months, use the following formula in cell C2.
=DATEDIF(0,B2,"M")
The syntax of the DATEDIF function is DATEDIF(start_date, end_date, unit).
Like EVALUATE, FILES, and GET.CELL, this function in Excel is not documented. Undocumented functions differ from ordinary functions because they are not listed in the function list and do not pop up when you type their name.
But these functions still work in Excel (you simply need to know the correct syntax).
We don’t have a start and end date to provide as arguments in this scenario. Only how many days are there between the two dates is all we know.
Still, we can use the DATEDIF function to calculate the number of months between the two dates. In other words, we can use the DATEDIF function in Excel to convert days to months.
To begin, enter 0 as the function’s first argument. Then, for the second argument, we have to enter or select the number of days.
In this example, we have to select cell B2. For the final argument, we have to put the letter M within quotation marks. This tells the function that we want to get the total number of completed months represented by the days.
It’s worth noting that the DATEDIF function calculates the number of completed months. The DATEDIF function’s result matches the ROUNDDOWN value of the number of days divided by (365/12).
Also read: How to Convert Date to Serial Number in Excel?
Method 3 – Using QUOTIENT Function to Convert Days to Months in Excel
When converting days to months in Excel, you sometimes don’t want to use a hidden function like the DATEDIF function.
If so, you can use Excel’s QUOTIENT function to convert days to months.
The table below is based on a debtor analysis. Column B shows the number of days from the invoice payment’s due date. In column C, you have to convert the days to months.
In cell C2, enter the following formula to calculate the given number of days in terms of months.
=QUOTIENT(B2,365/12)
The QUOTIENT function is useful in getting the integer part of a division result.
So, when we divide the number of days by the average number of days per month, we can use the QUOTIENT function to extract simply the number of months.
The syntax of the QUOTEINET function is QUOTIENT(numerator, denominator). The numerator of the function is the number of days.
So cell B2 can be selected as the numerator for the function. The denominator is the monthly average number of days.
Simply divide the average number of days per year by the number of months per year to get the average number of days per month. So, we divide 365 days by 12 months.
The QUOTIENT function, like the DATEDIF function, returns the number of completed months. If you want to count the incomplete month as one month, simply add a 1 to the end of the formula, as shown below.
=QUOTIENT(B2,365/12)+1
As soon as you add 1 to the QUOTIENT function, the period from day 1 to day 30 is regarded as month 1. The number of months for 15 days has changed to 1, as seen in cell C5.
Also read: Calculate Days Between Two Dates in Excel (Workdays/Weekends)
Method 4 – Using INT Function to Convert Days to Months in Excel
Another Excel function that can be used to convert days to months is the INT function. This is pretty similar to the QUOTIENT function in Excel.
The table below is an extract from a debtor analysis. The days from each invoice payment’s due date are shown in Column B. In column C, you have to convert days to months.
In cell C2, enter the following formula to calculate the given number of days in terms of months.
=INT(B2/(365/12))
In this example, we first divide the given number of days by the average number of days in a month.
The number of days to be divided in this example can be found in cell B2. We may calculate the average number of days in a month by dividing the average number of days in a year by the number of months in a year.
So we divide 365 days by 12 months to get the average days per month.
Then, we round down or remove the decimal part of the calculation’s result using the INT function.
A number is rounded down to the nearest whole number using Excel’s INT function. The INT function’s syntax is INT(number). In this case, the result of the number of months calculation formula is the argument for the INT function.
You’ve now learned four different methods to convert days to months in Excel.
To get the number of completed months or not, you can change these formulas as needed.
Other Excel articles you may also like:
