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

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

• you okay with two columns? or must you have one ? – Umar.H Oct 25 '18 at 19:36
• 1 column is preferred. Your solution with 2 columns seems to be working quite well, though. I'm still testing. – RamJet Oct 25 '18 at 21:20
• You could hide the split columns and re concat them, not that elegant but that's what comes to mind. – Umar.H Oct 25 '18 at 21:33
• Additionally you could multiply the time values by 24 to get the hourly value and then again by 60 to get the minute value, but it's not the most elegant solution – Umar.H Oct 26 '18 at 6:33

``````=QUOTIENT(ROUND((B1-A1)*24);24) & " days, " & MOD(ROUND((B1-A1)*24);24) & " hours"
``````

This would work quite well. Google sheet subtraction just yields the elapsed time in days, and you can convert it convert it into hours, round it into nearest hour, and convert it back into days and hours by taking quotient and remainder.

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.

• Not quite working. If the difference in days is 3, but it hasn't been a full 3 days, the display value in the second split cell displays a negative value for hours. For example, if the time lapsed is 68 hours, the two calculated cells read "3 days, -4 hrs". It should read, 2 days, 20 hrs. – RamJet Oct 26 '18 at 14:44