Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Real amateurs question -
I have a list of times formatted as hh:mm which i need to sum. If sum of the values is less than 1 day the function is sum(a1:a20), however if the total exceeds 24 hours the function needs to be sum(a1:a20)*24. I need a function that knows whether to use the standard sum or the x24 version, however a standard if statement doesn't work as the standard sum equation will always return a value less than 24 hours. One further complication is the formatting - in order to get a value greater than 24 hours to display the true value it needs fomatted as a general number, however values less than 24 hours need the hh:mm format. Example 1:00 8:00 5:00 6:00 9:00 sum(a1:a4) = 20:00 if i ask it to sum(a1:a5) it will return 05:00 (the hours leftover from one full day) if i ask it to sum(a1:a5)*24 and format it as a number it returns 29. Anyone any idea of function that can do all of this decision making by itself? so if ask excel |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Adding Times | Excel Discussion (Misc queries) | |||
adding times together | Excel Discussion (Misc queries) | |||
adding times | Excel Discussion (Misc queries) | |||
Adding up Times | Excel Worksheet Functions | |||
ADDING TIMES | Excel Worksheet Functions |