Find the Days and Hours Elapsed Using Google Sheets

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.

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)
• 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)

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 the end_date.
  • Then, use ROUNDDOWN to remove any partial days.
• 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)
  • 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)
• 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)
• 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"

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.

---