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

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.