Find the Difference Between Two Times on Different Days

Measuring how much time has passed between two times in Google Sheets may seem easy. But when those times are on different days, it can be tricky. Let’s take a look.

You can make a copy of the live Google Sheet used for these examples to follow along.

Valid Inputs

Before diving into the formulas, ensure the starting and ending values are valid times for the calculation to work. Since we are working across days, you must use the date and time.

There are several valid date formats that Sheets will recognize. Here are some examples:

List of valid date/times
Valid Date/Times

We can use any of the valid formats shown above, but we’ll use the first format shown – 4/4/2023 3:31 PM for the rest of this tutorial.

Another method to check for validity is the ISDATE function shown in cells B2 through B4. This function returns TRUE when it’s given a valid date.

Valid start and end times
Valid Start and End Times

Now that we have the input syntax for the starting and ending times let’s discuss the output.

Finding the Difference – Short Duration

There is a difference in the input and output values. The inputs are points in time, but the output is an amount of time. Google Sheets calls this output Duration and has special formatting available.

Duration formatting in the number menu
Duration Format

The duration format removes the AM/PM and allows the time to exceed 24 hours. Therefore, this is a good choice for showing the passage of time.

Once you have your date/times in an acceptable format, the formula for the difference is the easy part. That is, assuming the two times are within a few days.

A formula to find the time difference and the output formatted as a duration
Completed Short Duration Formula

Formula used: =(ending_date_time)-(starting_date_time)

We subtract the starting time from the ending time to arrive at the difference. If the days are farther apart, you must customize the formatting to get a readable answer.

Finding the Difference – Long Duration

You may want to show years, months, or days as the dates get further apart. Otherwise, you will be showing the duration in thousands of hours.

Too many hours
Too Many Hours

Duration formats are limited to hours or smaller, so we have to take a different approach to show years, months, or days elapsed. There is no easy formula for this, so we will use the TIMEDIF add-on developed by Sheets Help designed to calculate durations between two times and show the output in the units of your choice (years, months, minutes, etc.).

app icon for TIMEDIF with a calendar and clock
TIMEDIF – Google Sheets Add-On

Calculate any duration in:

  • Years
  • Months
  • Weeks
  • Days
  • Hours
  • Minutes
  • Seconds
  • Milliseconds

The TIMEDIF Add-On

After you install TIMEDIF, click the button on your sidebar to activate it.

The app in the sidebar that starts TIMEDIF
Icon that Starts TIMEDIF

After starting the app, you calculate the difference between two times on different days in just a few clicks.

YouTube Video Tutorial

YouTube player