Time additions dd:hh:mm

[Lee]

Hi.
I have a team of people that record jobs onto a system in the format of dd:hh:mm the sheet could be a weeks worth or a days worth depending on how many days I pull off to review. The column does not have a total and when I try to add the days hours and minutes booked it does not total. Is there a way to do this without a massive formula that takes a long time to input each time I download a sheet. It is downloaded in to an excel file if that makes any difference. Attached a small sample of the times but taken out the guys names. Thanks in advance for any help.
Lee

 

[shamimarita]

Hello Lee,

Thanks for sharing the sample. In your sheet, the values in column E are being treated as normal time values (hh:mm:ss), so Excel displays them as clock times instead of elapsed durations.

For this type of data, you can use a custom format that supports accumulated time properly.

Try these steps:

• Select the Time column.
• Press Ctrl + 1 → Number tab → Custom.
• Use this format:

[h]:mm:ss

• This will correctly display total elapsed hours without resetting after 24 hours.

If you specifically want a dd:hh:mm style output, use:
[d]:hh:mm

Then a SUM formula like:
=SUM(E3:E18)

• It will show the accumulated duration correctly.