Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. Then type a formula like one of the following.
Warning: If the Start_date is greater than the End_date, the result will be #NUM!.
In this example, the start date is in cell D9, and the end date is in E9. The formula is in F9. The “d” returns the number of full days between the two dates.
In this example, the start date is in cell D13, and the end date is in E13. The “d” returns the number of days. But notice the /7 at the end. That divides the number of days by 7, since there are 7 days in a week. Note that this result also needs to be formatted as a number. Press CTRL + 1. Then click Number > Decimal places: 2.
In this example, the start date is in cell D5, and the end date is in E5. In the formula, the “m” returns the number of full months between the two days.
In this example, the start date is in cell D2, and the end date is in E2. The “y” returns the number of full years between the two days.
Calculating the number of days between two dates is a common task in Excel Whether you’re planning a project timeline, tracking age, or analyzing data trends over time, knowing how to find the days between dates in Excel is an important skill
In this comprehensive guide, I’ll show you several easy methods to calculate the difference in days, weeks, months and years between two dates in Excel. You’ll learn how to:
- Find the number of days between dates by simple subtraction
- Use the powerful DATEDIF function
- Exclude weekends and holidays from date calculations
- Instantly calculate ages and lengths of service
- Subtract times to determine elapsed hours and minutes
After reading this, you’ll have the Excel date calculation skills to work smarter with any timeline or date-based analysis! Let’s dive in.
Overview of Calculating Date Differences in Excel
Before getting into the specifics, let’s review the key principles and functions involved in figuring out the time span between two dates in Excel:
Key Principles
-
Date formatting – Make sure your dates are formatted correctly as date values, not text strings
-
Start date and end date – You’ll always need a start date and end date to calculate the difference between
-
Date order – End date must be after start date, or you’ll get an error.
Key Excel Date Functions
-
=DATEDIF– Finds time units between two dates. -
=NETWORKDAYS– Counts workdays between dates excluding weekends. -
=NETWORKDAYS.INTL– Workdays minus weekends and holidays. -
=TODAY()– Returns current date on your computer.
Now let’s look at how to leverage these concepts and functions to find the number of days between two dates in Excel.
Subtract Dates to Get Days Between
The simplest way to find the difference in days between two dates is by subtraction. Just take the later date and subtract the earlier date.
For example:
Start Date: 1/1/2023End Date: 1/5/2023 Formula: =1/5/2023 - 1/1/2023Result: 4 daysThis approach works great for quick day calculations. But for more complex date math, Excel’s DATEDIF comes in handy.
Use DATEDIF to Calculate Days Between Dates
Excel’s DATEDIF function calculates the time span between two dates in days, months, and years.
To use DATEDIF to get days between dates:
-
Enter start and end dates in cells.
-
In another cell, use
=DATEDIF(start_date, end_date,"D")
This returns the total days between the dates.
For example:
Start Date: 1/1/2023 End Date: 2/15/2023Formula: =DATEDIF(A2,B2,"D")Result: 45 days DATEDIF handles leap years properly and can also find weeks, months, and years between dates too.
Calculate Net Workdays Between Two Dates
What if you only want to count net workdays between two dates, excluding weekends and holidays?
NETWORKDAYS and NETWORKDAYS.INTL have you covered.
To find workdays between dates:
-
Enter start date and end date.
-
Use
=NETWORKDAYS(start_date, end_date)to exclude weekends. -
Add a holiday range as a third argument to also omit holidays.
For example:
Start Date: 1/1/2023End Date: 1/31/2023 Formula: =NETWORKDAYS(A2,B2,Holidays)Result: 21 workdays (excluding weekends and 1/16 holiday)This function is perfect for determining business days for scheduling or timelines.
Calculate Age in Years, Months, and Days
You can easily calculate a person’s age or length of service using DATEDIF. Here’s how:
-
Enter birthdate or start date.
-
Use
=DATEDIF(start_date, TODAY(),"Y")to get years. -
Add
DATEDIF(start_date, TODAY(), "YM")for remaining months. -
Subtract dates to find leftover days.
-
Combine into one cell with text labels.
For example:
Birthdate: 2/27/1985 Formula: =DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months, " & (TODAY()-DATE(YEAR(A2)+DATEDIF(A2,TODAY(),"Y"),MONTH(A2),DAY(A2))) & " days" Result: 38 years, 1 months, 12 daysThis makes calculating precise ages and service histories a cinch!
Subtract Time Values to Get Elapsed Time
You can find elapsed time between two times in Excel by subtracting the start time from the end time.
Be sure to format cells as times and not dates before doing the calculation.
For example:
Start Time: 8:00 AM End Time: 4:30 PMFormula: =B2-A2 Result: 8:30 (8 hours, 30 minutes) This approach also works for calculating the time between two dates+times. Just ensure both have date+time formatting.
Subtracting times gives you the easy flexibility to find time spans in hours, minutes or seconds.
Quickly Switch Between Date Units
One last tip: You can convert between date units by dividing or multiplying days.
- Days * 7 = Weeks
- Days / 7 = Weeks
- Days * 365.25 = Years
- Days / 365.25 = Years
For example, to convert 45 days to weeks:
Formula: =45/7 Result: 6.43 weeksKnowing this math allows quick date unit conversions without functions.
Dates and Times Unlocked!
As you can see, Excel provides all the tools you need to calculate the difference between two dates down to the day, week, month, or year.
Whether you’re analyzing data over time, calculating ages, or determining workday schedules, these functions will make you a date calculation master!
Subtract the two times.
In another cell, subtract the start time cell from the end time cell.
Use a different formula to find days.
Now we need to find the number of remaining days. Well do this by writing a different kind of formula, shown above. This formula subtracts the first day of the ending month (5/1/2016) from the original end date in cell E17 (5/6/2016). Heres how it does this: First the DATE function creates the date, 5/1/2016. It creates it using the year in cell E17, and the month in cell E17. Then the 1 represents the first day of that month. The result for the DATE function is 5/1/2016. Then, we subtract that from the original end date in cell E17, which is 5/6/2016. 5/6/2016 minus 5/1/2016 is 5 days.
Warning: We dont recommend using the DATEDIF “md” argument because it may calculate inaccurate results.
How To Calculate The Number of Days Between Two Dates In Excel
FAQ
What is the formula for number of days in Excel?
|
Data
|
|
|
|
=DAYS(A2,A3)
|
Finds the number of days between the end date in A2 and the start date in A3 (364).
|
364
|
What is the formula for finding days in Excel?
|
Date
|
|
|
|
15-Apr-11
|
|
|
|
Formula
|
Description (Result)
|
Result
|
|
=DAY(A2)
|
Day of the date in cell A2 (15)
|
15
|
How to calculate days between dates in Excel?
The easiest way to calculate days between dates in Excel is by subtracting one date from another: For example, to find out how many days are between dates in cells A2 and B2, you use this formula: Where A2 is an earlier date, and B2 is a later date. The result is an integer that represents no. of days between two dates:
How to calculate the number of working days in Excel?
To calculate the number of working days (Column D) – when the start date, end date, and holidays are specified – use the below formula in D3 and copy for all cells: This function works great in most cases, except the ones where the weekends are days other than Saturday and Sunday.
How to calculate the number of days between a future date?
If I want to calculate the number of days between a future date, I have to enter today’s date as the start date and the other given date as the end date. If not, Excel returns the number of days between two dates as a negative number. Compared to the simple subtraction method in Formula 1, this function directly gives the result as a number.
How to calculate a date in Excel?
Here’s how you can do it: Select an empty cell where you want to display the answer. Enter the formula: =B1-A1, where B1 is the later date and A1 is the earlier date. This formula subtracts the earlier date from the later date. The result will be displayed as a negative number, so you need to format the cell to display it as a positive number.
