Google-sheets – In Google sheets, how to calculate elapsed duration between two cells, each containing date and time and display as days and hours

google sheetsgoogle-sheets-dates

There is a built-in duration format, but Elapsed days is not an option. I want to display "2 days, 3 hours"

Best Answer

Try the following:

=split(days(B2,A2) & " Day," & Timevalue(B2) - timevalue(A2),",")

Days allows you to work out the difference between two days, timevalue gives you the duration between two times as you have to extract it from your cells. I would then use split to put the duration a separate column as formats can only be applied to a single cell as far as I'm aware

you could also just use =Days and subtract the end date from the start to get the hours, but it won't prase it into days.

Related Solutions

Google-sheets – How to get the Elapsed time of two cells hh:mm in Google Spreadsheet

For times that span past midnight you cannot simply subtract. Or you'll get a negative number.

Start 
9:00 PM  

End   
5:38 AM 

Total 
-15:22:00

What you'll want to do (where start is column A and End is column B, etc.) is the following formula: =(B2-A2)+1

The one being equal to twenty-four hours. So: 9 pm to 5:38 am = 8:38.

Google Sheets – Calculate Days Between Now and a Future Date

There are two possible options for you to consider.

Formula

"28/06/2015"-TODAY())
DATEDIF(TODAY(), "28/06/2015", "D")

Todays date is: 28/10/2014. The result will be an integer (243).

Note

Make sure to format the outcome as a number and not a date. If you choose the NOW() formula, you introduce a time component. This will break up the calculation and show decimals.