How to calculate elapsed time in days, hours, minutes, and seconds in Google Sheets. We can find the elapsed days and time between two dates in Google Sheets using the INT function.

As an example, in cell A2 I have the timestamp (date time) in the format 08/01/2019 13:00:00 and in B2 it’s 10/01/2019 16:30:00.

Elapsed time is the time that goes by from the beginning of an event
(08/01/2019 13:00:00) to its end (10/01/2019 16:30:00).

What I want is the elapsed time in the format in days, hours, minutes and seconds like 2d 03:30:00. Which means the elapsed time is 2 days 3 hours and 30 minutes.

See how to create an elapsed time date calculator in Google Sheets using the built-in functions.

How to Calculate the Elapsed Days and Time Between Two Dates

First, I will show you how to calculate the elapsed days and time separately. Then we can learn how to format that as required.

Assume the above event start time is in cell A2 and end time in cell B2.

The Formula in cell C2 to get days elapsed.

Formula # 1:

=int(B2-A2)

The Formula in cell D2 to get time elapsed.

Formula # 2:

=text(B2-A2-int(B2-A2),"HH:MM:SS")

How to Format the Elapsed Days and Time Between Two Dates

I am going for a limited formatting and we can’t skip this as now the elapsed day is in one column and time in another column.

See the above screenshot. Actually what I am doing is combining both the formula outputs and separating the date and duration with “d”.

=C2&"d "&D2

You can replace the cell reference C2 and D2 with the formula # 1 and formula # 2 as below.

=int(B2-A2)&"d "&text(B2-A2-int(B2-A2),"HH:MM:SS")

Array Formula that Returns Elapsed Days and Time in Google Sheets

If you have multiple event start and end time, then better to go for an array formula.

Forming an array formula is not that much tough with supporting Google Sheets functions.

No doubt the function INT and the ampersand sign that I have used to combine elapsed days and time support the ArrayFormula function.

When forming an array formula, you must include the option to limit the formula output to non-blank rows. Otherwise, your spreadsheet may become very resource hungry.

Using LEN in IF Statment is that solution.

=ArrayFormula(IFERROR(if(Len(A2:A),(int(B2:B-A2:A)&"d "&text(B2:B-A2:A-int(B2:B-A2:A),"HH:MM:SS")),)))

The above formula returns the elapsed days and time between the dates in the range A2:B as follows.

Related Reading:

1. How to Compare Time Stamp with Normal Date in Google Sheets.
2. How to Deduct Lunch Break Time From Total Hours in Google Sheets.
3. Payroll Hours Time Calculation in Google Sheets Using Time Functions.
4. Google Sheets: The Best Overtime Calculation Formula.
5. How to Convert Military Time in Google Sheets.
6. Move Time In and Time Out to the Same Row in Google Sheets.