Excel
Apr 9, 2026
Calculating Date and Time Intervals with Excel Formulas
There are many reasons you might want to perform date and time calculations. We're going to look at the tips and tricks that will get you through calculating excel date formulas. Let's start with the functions themselves.
Key Takeaways
- Excel stores dates as serial numbers starting from January 1, 1900, making date math possible through simple addition and subtraction
- Use DATEDIF to calculate intervals in days, months or years between two dates
- WORKDAY and NETWORKDAYS functions exclude weekends and holidays from calculations
- Time values are decimals between 0 and 1, where 0.5 equals 12:00 PM (noon)
Excel Date Functions Quick Reference
Before diving into examples, here's a quick reference table of the date functions in excel covered in this guide:
| Function | Purpose | Basic Syntax |
|---|---|---|
| DATE | Enter a specific date | DATE(year,month,day) |
| TODAY | Return current date | TODAY() |
| DAY, MONTH, YEAR | Extract date components | DAY(date), MONTH(date), YEAR(date) |
| TIME | Enter a specific time | TIME(hour,minute,second) |
| NOW | Return current date and time | NOW() |
| HOUR, MINUTE, SECOND | Extract time components | HOUR(time), MINUTE(time), SECOND(time) |
| DATEDIF | Calculate date intervals | DATEDIF(start,end,unit) |
| EDATE | Add/subtract months | EDATE(start_date,months) |
| WORKDAY | Calculate workday end date | WORKDAY(start,days,[holidays]) |
| NETWORKDAYS | Count workdays between dates | NETWORKDAYS(start,end,[holidays]) |
| DAYS | Count days between dates | DAYS(end_date,start_date) |
Date and Time Entry Functions
Here are the functions you need to know to enter, return and create date calculations:
| Function | Description | Example |
|---|---|---|
| DATE(year,month,day) | Enter a specific date in your spreadsheet | =DATE(2024,12,15) returns December 15, 2024 |
| TODAY() | Returns the current date. Updates each time Excel performs the calculation. Does not require any arguments. | =TODAY() |
| DAY, MONTH, YEAR | Returns just the day, month or year from a date serial number | =MONTH(A1) |
| TIME(hour,minute,second) | Enter a specific time in your spreadsheet | =TIME(14,30,10) returns 2:30:10 PM |
| NOW() | Returns the current date AND time. Updates each time Excel performs the calculation. Does not require any arguments. | =NOW() |
| HOUR, MINUTE, SECOND | Returns the hour, minute or second portion of a time from a serial number | =HOUR(A1) |
Date and Time Calculation Functions
Here are some of the functions you need to know to calculate elapsed time and date intervals. The table below shows the syntax and purpose of each excel date calculation function:
Excel Time and Date Format Basics
It also helps to have a basis from which to understand how Excel works with dates and times.
Excel interprets dates and times as numbers. You might see 6/30/2024, but Excel is treating it as 45473 or 45473 days after January 1, 1900. All dates from that date forward are assigned a sequential serial number.
And then there's time. You might type in 8:30 AM, but Excel treats it as 0.354167. This decimal value represents a fraction of a 24-hour day. If you multiplied 24 by this number you would get 8.5 hours which is what 8:30 means.
To be clear, if you entered 8:30 PM, you'd get a different number. 0.854167. Multiply that by 24 and you get 20.5. On a 24 hour clock 20 is 8:00 PM and, of course .5 of an hour is 30 minutes.
It is important that dates and times be entered in the appropriate format in order to perform calculations on them. You’ll know if you have done it correctly because it will automatically right justify.
Performing Date Interval Calculations
Now let’s look at some real-world examples of using Excel to calculate date intervals.
Add or Subtract Days from a Start Date
To add a number of days to a date, or to subtract a number of days from a date, just add or subtract the number of days. You don’t need any special functions. Note that the number that you add or subtract is always the number of days. You cannot substitute months or years.
The WORKDAY function might also come in handy if you need to calculate only days that employees will be working. WORKDAY excludes Saturday and Sundays from its count (or you can specify workweek in Excel Options if your organization or employees take different days off). See cell C16 in the example below.
This function also allows you to specify non-weekend days, such as holidays or company events that should be excluded from the calculation. You will need to create a list of excluded dates, and then specify the range in the optional HOLIDAY argument of the function as seen in cell D18.
In this example you can see that the end date changed by 2 days when holidays were included.
Add or Subtract Months from a Start Date
To add a number of months to a date, use the EDATE(start_date,months) function.
“Start_date” is the date to which to add, and “months” is the number of months you wish to add. Continuing with the example above, =EDATE(B1,3)” returns August 1, 2024, which is three months after May 1, 2024.
To subtract a number of months from a date, use the EDATE() function with a negative number of months. “=EDATE(A2,-2)” returns February 1, 2024, which is two months before May 1, 2024.
Calculate Intervals Between Two Dates
Calculating intervals between two dates comes up when you are calculating ages or need to keep a project on schedule. Here are several methods for calculating date intervals.
Subtract – When you only need to calculate days (not years or months), the easiest way is to just subtract the cell with the end date from the cell with the start date.
DAYS function – The DAYS function provides almost the exact same functionality.
NETWORKDAYS – This function lets you calculate the number of workdays between two days. It will exclude Saturday and Sundays from the calculation.
You can see that over several months, the difference is quite noticeable! This is good for project management when you need to see how much real work time is left. Like the WORKDAYS function, NETWORKDAYS also offers an optional HOLIDAY argument to exclude additional non-work days.
DATEDIF – The DATEDIF function is very useful when you want to evaluate date intervals in something other than days, although it can also calculate days as well.
Performing Time Interval Calculations
Time math is one of the strengths of Excel. However, it helps to know some important information to make your timesheet calculations work smoothly.
Add and Subtract Times
When you need to add or subtract a fixed number of hours, minutes, or seconds to a time value, just add or subtract the way you would any decimals. If you add 4 hours to 11:30 AM, you get 3:30 PM:
To understand time arithmetic more fully, you need to understand first how Excel stores times.
Try this: in an empty cell, type “=NOW()” and hit <ENTER>. The result shows the current date and time, probably formatted like this: 6/26/2024 9:57
Next, right-click the cell, choose “Format Cells,” and pick “General.” The result should look something like this: 41816.41498
This is how Excel stores date/times—the date is the number to the left of the decimal, and time is the decimal portion of the number, where 0 corresponds to 12:00 AM, 1 is midnight, and every other time of the day falls somewhere in between. Times are decimals values between 0 and 1.
To convert a time to its decimal value, divide the hours (using the 24-hour clock) by 24 (the number of hours in a full day). Example: 12:00:00 PM (Noon) is exactly halfway through the day, so the decimal value is 1 / 2 = 0.5.
To convert a time decimal value to the number of hours, multiply by 24.
Because times are simply numbers between 0 and 1, you can do any arithmetic on times, just as you would with any other decimal numbers. Add them, subtract them, multiply them by two, divide them by four—just like any other decimal numbers.
But there are a couple of warnings as you get into time arithmetic!
Try this, using a 24-hour format:
Why Does 15 hours + 18 hours = 9 hours?
Right-click on Cells A1 through A3, choose “Format Cells,” and select “General.”
This shows that Excel did, in fact, add the two time values, which resulted in a number greater than one, meaning more than a full day. Like an odometer, however, Excel’s standard time formats roll over and show only the decimal portion—0.375, which equals 9:00:00.
When you need to show hours greater than 24, such as when adding up hours from a timesheet, choose a time format that displays the larger numbers:
Applying that format to this example produces 33 hours, as expected:
Create a Timesheet Calculator
Here is a quick example of time calculations in action:
The calculator uses simple time subtraction (Hours Worked) to determine the number of hours worked in each segment: time out – time in, and adds the two shifts together. The weekly total sums the Hours Worked column.
The format for hours worked and weekly total is “[h]:mm,” which ignores seconds and does not display AM or PM. The square brackets around the hours allow this formula to show values greater than 24 hours.
This is just the beginning of calculating formulas using times. From this point, you can develop far more complicated formulas, such as determining the average time required to complete a task, the maximum time between phone calls, etc. As long as you remember that times are just decimal numbers that represent portions of a day, you can work through any remaining calculations. You have the tools, and you know how to use them. Enjoy!
Common Date Formula Errors and How to Fix Them
Even experienced Excel users run into issues with common date formulas. Here are the most frequent problems and their solutions:
- Dates displaying as numbers: If you see 45473 instead of a date, right-click the cell, select Format Cells and choose a Date format. Excel stores dates as serial numbers, so this is a formatting issue, not a formula error.
- #VALUE! errors from text dates: When dates are stored as text, formulas won't work. Use DATEVALUE() to convert text dates to proper date values, or re-enter the dates in a recognized format.
- Regional date format conflicts: If you enter 3/4/2024, Excel may interpret this as March 4 or April 3 depending on your regional settings. Use the DATE function (e.g., =DATE(2024,3,4)) to avoid ambiguity.
- Negative time results: When subtracting times where the end time is earlier than the start time (like overnight shifts), you'll get a negative result. Add one to the formula to account for the day change: =(B1-A1)+1.
