Adding times

  • Thread starter Thread starter mel_flynn
  • Start date Start date
M

mel_flynn

I'm trying to add the following list of times:
09:30 + 09:00 + 09:00 + 04:00 + 08:30 + 08:30 + 06:00 + 05:00 + 05:00
07:00 + 08:00 + 05:00 + 04:00 + 07:30

Using the following formula:
=IF(INT(ROUND(SUM(L4:L70)/0.04166667,2)) = 0, ""
INT(ROUND(SUM(L4:L70)/0.04166667,2)) &":"
IF(MINUTE(MOD(SUM(L4:L70),1)) < 10, "0" & MINUTE(MOD(SUM(L4:L70),1))
MINUTE(MOD(SUM(L4:L70),1))))

The problem lies in the part of the function marked in bold. Thi
function is working perfectly with all/most other combinations o
times.

Hopefully someone can help me.

Thanks,
Imelda
 
It is not clear to me what you're trying to do.
Why not just SUM the times and format as time or Custom format as [h]:mm?
 
Nothing is bold as I see the message. However, it appears that you are
trying to display the sum of times that will exceed 24 hours. Instead
of working so hard, why not use a custom format of
[h]:mm

If there is some reason to continue your current approach,
SUM(L4:L70)*24 is more accurate, easier to type, and easier to
understand than SUM(L4:L70)/0.04166667

Jerry
 
Back
Top