Thread: Adding Times
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LiAD LiAD is offline
external usenet poster
 
Posts: 386
Default Adding Times

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