Adding times

mel_flynn · #1 11-30-2005, 06:37 AM

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. This function is working perfectly with all/most other combinations of times.

Hopefully someone can help me.

Thanks,
Imelda.

#2 11-30-2005, 09:45 AM

Imelda,

Have you tried simply summing the values:

=SUM(L4:L70)

and using the custom format

[h]:mm

or

[m]:ss

(depends on how those values are actually entered....)

HTH,
Bernie
MS Excel MVP

"mel_flynn" <mel_flynn.1zb5go_1133350803.0141@excelforum-nospam.com> wrote in message
news:mel_flynn.1zb5go_1133350803.0141@excelforum-nospam.com...
>
> 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. This
> function is working perfectly with all/most other combinations of
> times.
>
> Hopefully someone can help me.
>
> Thanks,
> Imelda.
>
>
> --
> mel_flynn
> ------------------------------------------------------------------------
> mel_flynn's Profile: http://www.excelforum.com/member.php...o&userid=29206
> View this thread: http://www.excelforum.com/showthread...hreadid=489376
>

Welcome to the Excel Forum

Forum Statistics