Elapsed Days and Time Between Two Dates in Google Sheets

Published on

When you have a start date and time along with an end date and time, you may need to calculate the number of elapsed days and time between the two timestamps.

This is useful for tracking durations, similar to a countdown timer, except instead of using the current time as a reference, you are working with predefined timestamps.

In this tutorial, you’ll learn how to calculate elapsed days, hours, minutes, and seconds between two timestamps in Google Sheets using built-in functions.

Generic Formula to Calculate Elapsed Days and Time

=INT(end-start)&"d "&TEXT(end-start-INT(end-start), "HH:MM:SS")
  • start – The start date and time.
  • end – The end date and time.

Example of Calculating Elapsed Days and Time in Google Sheets

To determine the elapsed time between two timestamps, we can use the INT function and TEXT function.

Example Scenario

  • Start Time (A2): 08/01/2019 13:00:00
  • End Time (B2): 10/01/2019 16:30:00

The elapsed time between these two timestamps is 2 days, 3 hours, and 30 minutes, formatted as:

2d 03:30:00

You can use the following formula in C2 to calculate it:

=INT(B2-A2)&"d "&TEXT(B2-A2-INT(B2-A2), "HH:MM:SS")

Let’s go through the steps to compute this.

Step 1: Calculate Elapsed Days

To get the number of full days elapsed, use this formula in C2:

=INT(B2-A2)

This extracts the integer part of the difference, which represents the number of whole days.

Step 2: Calculate Elapsed Time (Hours, Minutes, Seconds)

To calculate the remaining time beyond whole days, use this formula in D2:

=TEXT(B2-A2-INT(B2-A2),"HH:MM:SS")

This extracts only the time portion of the elapsed duration.

Calculate elapsed time using the INT function in Google Sheets

Step 3: Combine Days and Time

To format the elapsed duration as Xd HH:MM:SS, combine the results of the above formulas:

=C2&"d "&D2

Or, use a single formula for a more compact approach:

=INT(B2-A2)&"d "&TEXT(B2-A2-INT(B2-A2), "HH:MM:SS")

This formula dynamically calculates both days and time in one step.

Step 4: Apply an Array Formula for Multiple Rows

If you have multiple rows of start and end times, an ArrayFormula applies the calculation across the entire column:

=ArrayFormula(IF(LEN(A2:A), INT(B2:B-A2:A)& "d "&TEXT(B2:B-A2:A-INT(B2:B-A2:A), "HH:MM:SS"), ""))

This formula ensures that the calculation only applies to non-blank rows, improving efficiency.

Final Output

For multiple timestamps, the output will look like this:

Array formula to calculate elapsed days and time between two dates in Google Sheets

Related Reading

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

Reset SCAN by Another Column in Google Sheets and Excel

Resetting SCAN function results based on values in another column is a topic of...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

More like this

Use XLOOKUP in a Structured Table in Google Sheets (Single and Multiple Conditions)

This tutorial is for users who want to apply XLOOKUP inside a structured table...

How to Get the Fastest Time for Each Person in Google Sheets

Whether you’re tracking race results, event times, or any other timed activities, finding the...

Highlight the Smallest N Values in a Column in Google Sheets

Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.