ExcelInsider Team
Counting months is necessary to keep track of project timelines, financial planning, managing schedules, etc. Excel has several date functions such as MONTH, YEARFRAC, and DATEDIF to count the number of months passed from a certain date to the present day.
➤ Choose a column cell where you want to put the counted months and type the following formula:
=DATEDIF(D2, TODAY(), “m”)
➤ Instead of D2, enter the cell reference that contains your start date.
➤ Drag the formula down using the fill handle if you want similar results for more cells.
While the formula works, the DATEDIF function gives the complete months between dates, excluding the extra days. In this article, we’ll cover all the formulas you can use to count months between two dates and how to alter them depending on the outcome you prefer.
Counting Months with the MONTH Function
Let’s take a project management dataset with start dates to demonstrate the formulas. Our column Start Date indicates the assigning dates of the corresponding tasks.
In the Months Passed column, we’ll count how many months have passed from the start date till today. The function TODAY() is used to input the present date according to your device’s selected region. In our case, today’s date is 24-05-2025.
If your start date and present day are in the same year, the MONTH function is the easiest solution to calculate their difference in calendar months. It extracts the current month number from today’s date and subtracts the start date’s month number.
As a result, the function returns the number of complete months between the two dates. For dates in the same month, the formula returns 0. Here’s how to apply it:
➤ Select a neighboring cell to the first date of your Start Date column. Type the following formula:
=MONTH(TODAY()) – MONTH(D2)
➤ Instead of D2, put the correct cell reference where your starting date is. You can also select the cell containing the date.
➤ Press Enter and use the tiny + sign (fill handle) on the bottom corner of the cell to drag the formula down to the column.
➤ Below are the counted months without considering the days and years. Notice how the formula returns 1 in the E7 cell even though a complete month hasn’t passed (counting from 26-04-2025 to 24-05-2025). Also, it returns 0 for the E10 cell as both dates are in the same month.
Applying the DATEDIF Function
Although it’s a hidden Excel function, DATEDIF is an accurate and powerful function for calculating the difference between two dates. To use the function to count the complete months between dates, follow these steps:
➤ Choose the cell where you want to put the calculated months and type the following formula:
=DATEDIF(D2, TODAY(), “m”)
➤ Replace D2 with the cell reference that contains your start date. Here, “m” signifies that we only want to calculate the month difference between two dates.
➤ Click Enter and use the fill handle to drag the formula down to the end of the dataset.
➤ In the final result, the formula returns 0 for both E7 and E10 cells, counting the full months only.
Rounding Up Month Differences with MONTH and YEAR Functions
With this method, we can calculate the month differences including partial months. The functions round up the result by one month if today’s day of the month is on or after that particular day of the starting date. Here’s how to apply them:
➤ Select the cell to enter the counted months and type the following formula:
=(YEAR(TODAY())-YEAR(D2))*12+MONTH(TODAY())-MONTH(D2)+(DAY(TODAY())>=DAY(D2))
➤ Here, you need to put the cell reference of your data’s start date instead of D2.
➤ Press Enter and drag the formula down using the fill handle.
➤ In the final result, check out the E7 and E10 cells where the formula returns 1, counting the partial months.
Counting Partial and Whole Months with the YEARFRAC Function
In this case, we’ll manipulate the YEARFRAC function to get the number of months between two dates. You can either count the number of months with decimals (including partial months) or round them up to the nearest whole number. Here’s how:
➤ Click on the first cell of the column for counted months and type the following formula:
=INT(YEARFRAC(D2,TODAY())*12)
➤ If you want to count partial months with decimals, enter this formula instead:
=YEARFRAC(D2,TODAY())*12
➤ Press Enter and drag the formula down to the remaining cells.
➤ Here’s the result with the INT function. Similar to the DATEDIF function, this formula also returns 0 for E7 and E10 cells.
➤ Without the INT function, the result will be like the following picture:
Note:
You can use the ROUNDUP function to count any part of a month as a full month. Meaning that even if one day of a month has passed, Excel will count it as a whole month. For this, use this formula:
=ROUNDUP(YEARFRAC(D2, TODAY()) * 12, 0)
Creating Your Own Excel Formula with VBA
With the VBA Editor, you can easily create a custom function to calculate the number of full months between a specific date and today’s date. Follow the steps given below:
➤ Look for the Developer tab in the top ribbon. If it’s not there, go to Files >> More >> Options.
➤ In the new dialog box, click on Customize Ribbon and choose Developer from the given options. Press Ok.
➤ Open the Developer tab and click Visual Basic. You can also use the keyboard shortcut ALT + F11 .
➤ In the VBA Editor page, click Insert and choose Module.
➤ In the new module dialog box, paste the following code:
Function CountMonths(StartDate As Date) As Integer
' Declare variables to hold year, month, and day differences/values
Dim yearDiff As Integer
Dim monthDiff As Integer
Dim dayStart As Integer
Dim dayToday As Integer
' --- Calculate the initial difference in years and months ---
' Get the difference in years between today and the start date
yearDiff = Year(Date) - Year(StartDate)
' Get the difference in months (0-11) within the current year/start year
monthDiff = Month(Date) - Month(StartDate)
' --- Get the day components for adjustment ---
' Extract the day of the month from the StartDate
dayStart = Day(StartDate)
' Extract the day of the month from today's date
dayToday = Day(Date)
' --- Calculate the total months difference initially ---
' Convert year difference to months and add the month difference
CountMonths = (yearDiff * 12) + monthDiff
' --- Adjust for incomplete months based on day comparison ---
' If the current day of the month is numerically earlier than the
' start day of the month, a full month has not yet completed.
' For example: StartDate = Jan 15, CurrentDate = Feb 10.
' yearDiff = 0, monthDiff = 1. CountMonths initially 1.
' dayToday (10) < dayStart (15) is TRUE. So, CountMonths becomes 0.
If dayToday < dayStart Then
CountMonths = CountMonths - 1
End If
End Function
Sub CountMonthsFunction()
End Sub➤ Now, go back to the Excel tab and type the following formula in a cell where you want to insert the counted months:
=COUNTMONTHS(D2)
➤ Replace D2 with the cell that contains the start date.
➤ Press Enter and drag the formula down. This formula returns the same result as DATEDIF for the E7 and E10 cells.
➤ To save the changes, go to the File tab and click on Save As.
➤ Browse and locate the folder where you want to save the Excel workbook.
➤ After selecting the folder, click on the arrow beside the Save As Type box and click on Excel Macro-Enabled Workbook. Press Save to protect the VBA macro.
Frequently Asked Questions
What is the #NUM error in DATEDIF?
For future dates, DATEDIF returns a #NUM error. So, if your start date is in the future/after TODAY(), use the following formula instead.
=IF(D2>TODAY(), -DATEDIF(TODAY(),D2,”m”), DATEDIF(D2,TODAY(),”m”))
Replace the cell numbers for your original cell references for start and end dates. It will fix the error and display the counted months in negative numbers.
How do I add months from today in Excel?
To add months from today’s date, insert the following formula in your preferred cell:
=EDATE(TODAY (), 6)
Replace 6 with the exact number of months you want to add to today’s date. The EDATE function will add or subtract specified months from a specified date.
How do I calculate working days from date to today in Excel?
If you want to calculate the number of working days from a date to today, type the formula given below in your chosen cell:
=NETWORKDAYS(D2, TODAY())
Instead of D2, enter the cell with your start date. The NETWORKDAYS function excludes weekends (Saturday and Sunday) by default.
Concluding Words
For the most accurate calculation, use the formula DATEDIF functions. It accurately counts the completed months between two dates. To count partial months, choose the function with YEAR and MONTH. Consider your data type and what outcome you want while choosing any of the above-mentioned formulas to count the difference between a specific date and today’s date in months.
