1

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

4
  • you okay with two columns? or must you have one ?
    – Umar.H
    Commented Oct 25, 2018 at 19:36
  • 1 column is preferred. Your solution with 2 columns seems to be working quite well, though. I'm still testing.
    – RamJet
    Commented Oct 25, 2018 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
    Commented Oct 25, 2018 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
    Commented Oct 26, 2018 at 6:33

2 Answers 2

1
=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.

0

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.

1
  • 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
    Commented Oct 26, 2018 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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