Finding the total days and hours between two times in Google Sheets is tricky. We’ll cover a few ways with examples you can use in your own spreadsheet.
Contents
Preparation – Properly Format Your Times
Before you use the formulas in this tutorial, ensure your times are valid. If the dates are invalid, they aren’t numbers, so these steps won’t work.
As shown above, you can use the ISDATE function to check each date.
Calculations – Find the Difference Between Two Times in Days and Hours
Once your data is ready, you can find the days and hours. We will cover several ways to find the time difference. Each technique has different advantages.
Days and Hours in Different Cells
We can split the output into different columns to simplify the formulas. One column will be Days
; the other will be Hours
.
Days formula: =ROUNDDOWN(end_datestart_date)
The formula subtracts the start_date
from the end_date
. Then we use ROUNDDOWN to remove any partial days. We will use the partial days in the formula for hours.
⚠️ Do not use the DAYS function. This function discards the times which are relevant for us.
Remaining hours formula: =ROUNDDOWN(((end_timestart_time)TRUNC(
end_timestart_time
))*24)
The formula needs more explanation. Its purpose is to calculate the hours elapsed after the last full day. Let’s break it down.
 To arrive at the number of hours as a fraction of one day:
 First, find the total amount of time passed as a decimal number.
end_timestart_time
 Next, subtract a truncated version of this number from itself to leave just the fractional value.
(end_timestart_time)TRUNC(
end_timestart_time
)
 First, find the total amount of time passed as a decimal number.
 To convert this fractional value to hours
 Then, multiply this resultant fractional value by
24
(hours in a day).((end_timestart_time)TRUNC(end_timestart_time))*24
 Last, round the result down to report only the hours.
ROUNDDOWN(((end_timestart_time)TRUNC(end_timestart_time))*24)
 Then, multiply this resultant fractional value by
If having the days and hours in separate cells is sufficient, you have all you need. However, you may want to show the days and hours in one cell. We’ll discuss combining these formulas next.
Days and Hours in the Same Cell
Our formatting options for showing durations stop at hours. You can show time durations as a custom number format using days, weeks, months, and years. To work around this limitation, we will convert the result to text.
⚠️ The drawback of converting the output to text is that you can no longer sort the value like a number or feed it into other formulas that expect numbers.
The formula for output in one cell: =TEXT(ROUNDDOWN(end_datestart_date),0)&" days, "&TEXT(ROUNDDOWN(((end_datestart_date)TRUNC(end_datestart_date))*24),0)&" hours"
The formula is quite long and challenging to read. Here is what is happening inside the formula.
 For the days:
 First, subtract the
start_date
from theend_date
.  Then, use ROUNDDOWN to remove any partial days.
 First, subtract the
 For the hours:
 To arrive at the number of hours as a fraction of one day:
 First, find the total amount of time passed as a decimal number.
end_timestart_time
 Next, subtract a truncated version of this number from itself to leave just the fractional value.
(end_timestart_time)TRUNC(
end_timestart_time
)
 First, find the total amount of time passed as a decimal number.
 To convert this fraction value to hours
 Then, multiply this resultant fractional value by
24
(hours in a day).((end_timestart_time)TRUNC(end_timestart_time))*24
 Last, round the result down to report only the hours that have passed.
ROUNDDOWN(((end_timestart_time)TRUNC(end_timestart_time))*24)
 Then, multiply this resultant fractional value by
 To arrive at the number of hours as a fraction of one day:
 Convert the days and hours to text.
 Wrap the two formulas inside the TEXT function and specify
0
as the format.TEXT(duration_formula,format)
 Wrap the two formulas inside the TEXT function and specify
 Join the four text parts.
 The & is an operator that joins multiple pieces of text into one string.
 [Number of days] days, [number of hours] hours
=TEXT(ROUNDDOWN(end_datestart_date),0)&" days, "&TEXT(ROUNDDOWN(((end_datestart_date)TRUNC(end_datestart_date))*24),0)&" hours"
 The & is an operator that joins multiple pieces of text into one string.
Now you have one string as your output for days and hours. As you can see, this formula is complex and fragile. Use it if it works for you. However, Sheets Help has a more straightforward solution.
TIMEDIF AddOn
For a mixed output with years, weeks, or months, consider using the TIMEDIF addon. This addon creates an accurate result with no code.
TIMEDIF will save you the time and effort of struggling through formulas by doing the calculations for you.
Live Examples in Google Sheets
You can practice with the examples in this tutorial by making a copy of the linked Google Sheet.
Related Tutorials

TRAVELDIRECTIONS Function – Google Sheets
Learn how to use the TRAVELDIRECTIONS function to write directions directly into Google Sheets.

TRAVELROUTEURL Function – Google Sheets
Learn how to use the TRAVELROUTEURL function in Google Sheets to create links to Google Maps directions.

TRAVELTIME Formula – Google Sheets
Learn how the TRAVELTIME formula calculates the trip duration between two locations.

Travel Formulas – Time and Distance Formulas in Google Sheets
Use the Travel Formulas addon to calculate time and distance in Google Sheets.

How to Calculate Age in Minutes Using Google Sheets
Learn how to calculate age in minutes using Google Sheets.

How to Calculate Age in Hours Using Google Sheets
Learn how to find the number of hours between two times in Google Sheets.