Working with time DURATION, not time of day

Solution 1:

You can easily do this with the normal "Time" data type - just change the format!

Excels time/date format is simply 1.0 equals 1 full day (starting on 1/1/1900). So 36 hours would be 1.5. If you change the format to [h]:mm, you'll see 36:00.

Therefore, if you want to work with durations, you can simply use subtraction, e.g.

A1: Start:           36:00 (=1.5)
A2: End:             60:00 (=2.5) 
A3: Duration: =A2-A1 24:00 (=1.0)

Solution 2:

Use format d "days" h:mm:ss or [h]:mm:ss, depending on your needs.

Say you have a duration of 30h 12m 54s:

• h:mm:ss -> 6:12:54 (not correct for a duration)
• [h]:mm:ss -> 30:12:54
• d "days" h:mm:ss -> 1 days 6:12:54

Variations are possible: I like something like d"d" h"h" mm"m" ss"s" which formats as 1d 6h 12m 54s.

Solution 3:

The custom format hh:mm only shows the number of hours correctly up to 23:59, after that, you get the remainder, less full days. For example, 48 hours would be displayed as 00:00, even though the underlaying value is correct.

To correctly display duration in hours and seconds (below or beyond a full day), you should use the custom format [h]:mm;@ In this case, 48 hours would be displayed as 48:00.

Cheers.

Solution 4:

With custom format of a cell you can insert a type like this: d "days", h:mm:ss, which will give you a result like 16 days, 13:56:15 in an excel-cell.

If you would like to show the duration in hours you use the following type [h]:mm:ss, which will lead to something like 397:56:15. Control check: 16 =(397 hours -13 hours)/24