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 times 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. Type =ISDATE
and use the cell reference of the date to check. To check the date in A2
, type =ISDATE(A2)
.
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
First, let’s look at split the output into different columns to simplify the formulas. One column will be Days
; the other will be Hours
.

Days formula: =ROUNDDOWN(end_date-start_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_time-start_time)-TRUNC(
end_time-start_time
))*24)
The formula’s 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_time-start_time
- Next, subtract a truncated version of this number from itself to leave just the fractional value.
(end_time-start_time)-TRUNC(
end_time-start_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_time-start_time)-TRUNC(end_time-start_time))*24
- Last, round the result down to report only the hours.
ROUNDDOWN(((end_time-start_time)-TRUNC(end_time-start_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
You can show time durations as a custom number format using days, weeks, months, and years but not hours.

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_date-start_date),0)&" days, "&TEXT(ROUNDDOWN(((end_date-start_date)-TRUNC(end_date-start_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_time-start_time
- Next, subtract a truncated version of this number from itself to leave just the fractional value.
(end_time-start_time)-TRUNC(
end_time-start_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_time-start_time)-TRUNC(end_time-start_time))*24
- Last, round the result down to report only the hours that have passed.
ROUNDDOWN(((end_time-start_time)-TRUNC(end_time-start_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,0)
- 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_date-start_date),0)&" days, "&TEXT(ROUNDDOWN(((end_date-start_date)-TRUNC(end_date-start_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 Add-On
For a mixed output with years, weeks, or months, consider using the TIMEDIF add-on. This add-on creates an accurate result with no formulas.
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
-
How to Add or Subtract Years in Google Sheets
Learn how to move dates forward for backward by whole years.
-
Weather Stats – Google Sheets Add-On
Use the Weather Stats add-on to import historical or forecast weather elements into Google Sheets.
-
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 add-on to calculate time and distance in Google Sheets.