Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding times
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding times | Excel Worksheet Functions | |||
adding times again | New Users to Excel | |||
Adding times | Excel Worksheet Functions | |||
Adding times | New Users to Excel | |||
Adding Times | Excel Programming |