Adding Times

[LiAD]

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

 

[Ashish Mathur]

Hi,

I am not sure if I understood your question 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

 

[Stefi]

Custom format the result (SUM) cell like [h]:mm

Regards,
Stefi

„LiAD†ezt írta:

 

[LiAD]

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

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

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

 

[Stefi]

=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

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

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

 

[Bob Phillips]

If all of the cells are formatted as hh:mm as you originally said, Ashish's
solution will work. You would only multiply by 24 if you wanted to convert
time to decimal, and that would apply to them all if all of the cells are
formatted as hh:mm .

--
__________________________________
HTH

Bob

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

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

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

 

[David Biddulph]

You can't change between time and general formats based on the value, but
you can produce a text output, such as
=IF(SUM(A$1:A6)>=1,TEXT(SUM(A$1:A6)*24,"General"),TEXT(SUM(A$1:A6),"hh:mm"))
--
David Biddulph

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

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

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

 

[LiAD]

stupid but i hadn't thought of formatting them all like a decimal and that
works fine.

cheers for ur help

Custom format the result (SUM) cell like [h]:mm

Regards,
Stefi

„LiAD†ezt írta:

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