Excel Formula: Count Days, Week, Month or Year Between Two Dates
In this tutorial, it provides the formulas to count the days, weeks, months, or years between two given dates.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
Count days
| DATEDIF(start_date,end_date,”d”) |
Count weeks
| DATEDIF(start_date,end_date,”d”)/7 |
Count months
| DATEDIF(start_date,end_date,”m”) |
Count years
| DATEDIF(start_date,end_date,”y”) |
Syntaxt and Arguments
| Start_date, end_date: The two dates that you want to count the difference between. If the start_date is larger than end_date, the formula will return an error value #NUM!. |
Remark
The DATEDIF function only works in Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007 versions.
How this formula works
Take an example, to count days between the dates in cells B3:C3, please use below formula:
| =DATEDIF(B3,C3,"d") |
Press Enter key.
Then drag auto fill handle down to the cells to count the days between other date ranges.
Following above to count weeks, months, or years:
Note：
When counting months between two dates by using the formula DATEDIF(start_date,end_date,”d”)/7, it returns a value in date format, you need to format the result as general or number as below screenshot shown.
Explanation
DATEDIF function: returns the years, months or days between two dates.
Relative Formulas
- Count Weekdays only between two dates
This tutorial provides formulas to count the days excluding weekends and holidays between two dates - Count Days Left In Current Month Or Year
Introducing formulas for counting the days left in current month or year. - Convert Time to Decimal Hours
Here provides a formula that converts time to decimal hours in Excel and explains how the formula works. - Convert Time To Decimal Minutes
In this tutorial, it provides a formula for converting time to decimal minutes in Excel.
Relative Functions
- Excel IF Function
Test for specified conditions, then returns the corresponding values - Excel VALUE Function
Convert text to number. - Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date. - Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date - Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.