Working with dates is an essential skill for any Excel user. Being able to convert between different types of date units, such as days to months, is incredibly valuable. For example, you may need to keep track of time worked on a given project. Aggregating data to monthly levels can be beneficial for summary reporting. In this tutorial, you’ll learn how to convert days to months in Excel.
By the end of this tutorial, you’ll have learned the following:
- How to convert days to months in Excel
- How to calculate the number of months a date range covers as whole numbers in Excel
How to Calculate the Number of Months Between Two Dates in Excel
How can you convert days to months in Excel?
To convert the number of days to months in Excel, you can divide the number of days by 365/12. For example, if your start date is in cell A2 and your end date is in cell B2, you can use =(B2-A2)/(365/12).
Calculating months can be tricky work. Because different months have different numbers of days, it can be difficult to get an exact number. One way is to assume that each month has on average 30 days. However, using the formula 365/12 is more accurate. This divides the number of days in a year by the number of months and returns a value of 30.4167.
Let’s see how we can use Excel to calculate the number of months between two dates in Excel:
How to Calculate Number of Months Between Two Dates in Excel
Load Your Dataset in Excel
In the example above, we’re working with a dataset that contains five different sets of dates. The first column contains our start date and the second contains the end dates.
Subtract the Start Date From the End Date
We first calculate the number of days between the two dates. Because Excel stores dates as numbers, we can simply subtract the start date from the end date. This returns a whole number of days between the two dates.
Divide the Number of Days by the Average Days Per Month
To get the number of months between the two dates, divide the number of days by 365/12. In order to do this, we can use the formula: =C2/(365/12).
Drag the Fill Handle Down to Calculate Months for Each Set of Days
To calculate the number of months for each set of dates, drag the fill handle down all the way. In this example, we’re using the fill handle when we select both C2 and D2 to calculate both sets of values.
In the example above, we split the calculations across multiple columns. However, you could also place it into a single column using the following formula:
=(B2-A2)/(365/12)In some cases, you’ll want to know just how many months two dates represent, rather than a fractional result. For example, you may want to know how many months have passed in a given duration. In the following section, you’ll learn how to calculate the whole number of months between two dates.
How to Calculate the Number of Whole Months From Two Dates in Excel
In this section, you’ll learn how to calculate the whole number of months from two dates in Excel. In the previous section, you learned how to calculate a fractional month. However, you may simply want to know how many whole months have passed between two dates.
This is where the Excel DATEDIF() function comes in handy. The function lets you easily calculate the number of different date units, such as days, months, and years between two dates. Let’s see how we can use the function to calculate the number of whole months between two dates:
Step 1: Load Your Dataset
In this example, we’re using the same values as we did in our previous example. We have a column containing start dates and end dates. We want to calculate the number of months between the two dates.
Step 2: Use the DATEDIF() Function to Calculate the Number of Months
The DATEDIF() function accepts the following arguments:
start_date=, which represents the start date of our valuesend_date=, which represents the end date of our valuesunit=, which in this case isM, representing the number of complete months
In our example above, this represents XYZ months.
Step 3: Drag the Fill Handle Down to Calculate the Number of Months for All Cells
By dragging the fill handle all the way down, Excel copies the formula for all values. This allows you to easily calculate the number of complete months between a whole range of dates.
Conclusion
In this tutorial, you learned how to calculate the number of months between two dates in Excel. Being able to work with months can be a tricky topic since some months have different dates. Because of this, you learned two different ways of calculating the number of months between two dates.
You first learned how to calculate the number of months represented by a fractional value. In this case, we assume that each month is made up of an average number of days. From there, you learned how to use the DATEDIF() function to calculate the number of whole months between two dates. This can help you calculate the number of complete months that have passed between a start date and an end date.
Additional Resources
To learn more about related topics, check out the tutorials below: