Convert Days to Months in Excel

In this article, I will show you how to convert days to months in Excel using a couple of simple formulas.

This could be useful when you are creating a project plan that has a start and an end date, and you want to know how many months there are between the start and the end date.

Let’s see how to do this using a simple example.

Convert Days to Months Using DATEDIF Function

Below is a data set with employee names in column A, their start date (or joining date) in column B, and their end date (resignation date) in column C.

Using this data, I want to know how many months a person has been employed in the company.

This can be done easily using the DATEDIF function.

Convert Days to Completed Months

Here is the formula that will do this:

=DATEDIF(B2,C2,"M")

Enter this formula in cell D2 and then copy it for all the other cells.

Note that this formula only gives me the total number of completed months between the two dates, and ignores any other additional days after the completed month.

The above DATEDIF formula takes three arguments:

• Start_Date – This is the start date that we have in cell B2
• End_Date – This is the end date that we have in cell C2
• Code – This is a code that tells the function what calculation needs to be done for the given number of calculated days between the start date and the end date. Here I have used “M” which will give me the total number of completed months in between the two given dates.

Convert Days to Completed Months and Days

If you want to know the total number of completed months as well as the days after the completed months, you can use the below formula:

=DATEDIF(B2,C2,"M")&" M "&DATEDIF(B2,C2,"MD")&" D"

The above formula gives you the result as a text string and uses the DATEDIF formula two times – first to calculate the total number of months in between the given dates and then the total number of remaining dates after the months have been counted

Here are the two formulas I have used:

• DATEDIF(B2,C2,”M”) – Here, I have used “M” as the third argument, which gives me the total number of completed months between the two dates
• DATEDIF(B2,C2,”MD”) – Here, I have used “MD” as the third argument, which gives me the number of days left after the total number of completed months is removed

Note: DATEDIF has been kept for compatibility purposes with Lotus 123. Microsoft recommends using this function with caution, as it can give you incorrect results in some scenarios.

Also read: Calculate Number of Weeks Between Two Dates

Convert Days to Months Using Simple Division

If you do not want to use the DATEDIF function for some reason, let me show you another way to do this.

In this method, we will be dividing the total number of days by an approximate number of month values.

Since there are 365 days in a year (assuming a non-leap year) and 12 months in a year, we can get an average number of days per month by dividing 365 by 12.

We can then use this value as the criteria to convert a given number of days into two months.

Below I have a data set where I have the start date in column B and end date in column C, and I want to calculate the total number of months in between the two dates.

Here is the formula that will do this:

=(C2-B2)/(365/12)

Enter this formula in cell D2 and then copy it for all the cells in the column.

This gives us the month value with a decimal portion that denotes the remaining number of days.

For example, 4.8 tells us that there were four completed months and 0.8 portions of the remaining month in between the dates.

If you only want the number of months without the decimal portion, you can use the below formula:

=INT((C2-B2)/(365/12))

One obvious drawback of this method is that it considers every month to be ~30.41 days long and ignores the fact that there could be 28, 29, 30, or 31 days in months.

If you want more accuracy, then you’re better off using the DATEDIF function method.

In this article, I showed you how to convert days into months in Excel using two simple formulas.

I hope you found this article helpful. If you know of any other method to do this or you have any feedback for me, please leave them in the comment section below.

Other Excel articles you may also like:

• How to Get the Number of Days in a Month in Excel?
• How to Add or Subtract Days to a Date in Excel
• How to Calculate the Number of Days Between Two Dates in Excel
• How to Get Month Name from Date in Excel
• Check IF a Date is Between Two Given Dates in Excel
• Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
• Convert Month Name to Number in Excel