=SUM(A1:A4)*24 returns 20 if formatted like general
=SUM(A1:A5)*24 returns 29 if formatted like general

Regards,
Stefi

€žLiAD€� ezt Ã*rta:

Yes I think u've misunderstood. That works but only if the user
automatically goes through the data and formats the times that need h:mm, and
those that need the sum*24 formula and format as a general number. The
purpose of this sheet is to automatically sort and arrange data according to
certain text strings. I have lists of times that i will sum providing
certain conditions are met - the conditions bit i can deal with but not a
formula that does;

sum if the total is less than 24 and format as hh:mm, or
sum*24 and format as general number if the sum is greater than 24.

I need a function that looks at the data, picks the sum function and format
it needs and returns the corresponding result.

Thanks

"Ashish Mathur" wrote:

Hi,

I am not sure if I understood your queston completely. However try this.
Just use the simple SUM() function and format the cell as [h]:mm


--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LiAD" wrote in message
...
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