# 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 dates are invalid, they aren’t numbers, so these steps won’t work. There are several formats; choose the one that works best for you.

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_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 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_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 that have passed.
• `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

Our formatting options for showing durations stop at hours. You can show time durations as a custom number format until you get to 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_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,format)`
• 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 – No Formulas

For a mixed output with years, weeks, or months, consider using the TIMEDIF add-on. This add-on creates an accurate result with no code.

### 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 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.

• ###### Find the Weeks and Days Between Two Dates

Learn how to find the weeks and days between two dates.

• ###### Find the Number of Weeks Between Two Dates

Learn how to find the number of weeks elapsed between two dates.

• ###### Find the Days and Hours Elapsed Using Google Sheets

Find the number of days and hours elapsed between to times.

• ###### Difference Between Two Times in Seconds Using Google Sheets

Find the difference between two times in seconds.