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 date and time intervals with Excel formulas. Let’s start with the functions themselves.

Date and Time Entry Functions

Here are some of the functions you need to know to enter, return, and create date calculations:

DATE(year,month,day)         Enter a specific date in your spreadsheet.

=DATE(2024,12,15) returns the date December 15, 2024.

TODAY()                                 Returns the current date. Updates to current date each time Excel performs the calculation. TODAY() does not require any arguments; it always returns the current date.

DAY, MONTH, YEAR              Returns just the day, month, or year from a date serial number.

TIME(hour,minute,second) Enter a specific time in your spreadsheet.

                                                =TIME(14,30,10) returns the time 2:30:10 PM

NOW()                                     Returns the current date AND time. Updates to current time each time Excel performs the calculation. NOW() does not require any arguments; it always returns the current time.

HOUR, MINUTE, SECOND Returns the hour, minute or second portion of a time from a serial number.

Date and Time Calculation Functions

Here are some of the functions you need to know to calculate elapsed time and date intervals:

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!