Summing Time Data

[AJG]

Hi.
I have numerous schedules (one on each sheet). I also have a Summary sheet
in which I sum the information (linked) on each sheet.
I'm trying to do a SUM function to the time data, but I keep getting zero as
the result. However, if I manually add the data (Sheet1!A1+Sheet2!A1,etc.)
it works.
Again, the data is in time format "h:mm."
Is there anyone out there who knows why the SUM function won't work, but
manual addition will?

Thanks a lot.

[JE McGimpsey]

SUM() ignores text, while the + operator will coerce its text arguments
to numeric if it can.

Try coercing your data to actual numeric values (which is how XL stores
times - as fractional days) by copying an empty cell, selecting your
values, choosing Edit/Paste Special, selecting the Values and Add radio
buttons, then clicking OK. Format the result as time.



In article <[email protected]>,
AJG <[email protected]> wrote:

> Is there anyone out there who knows why the SUM function won't work, but
> manual addition will?

[CCW]

I don't understand this answer. I have a column of times, summing to zero.
I have tried the formula, SUM(G2:G10)*24, formatting the cell [h]:mm, both
found in the help menu. I have tried the formula with the + operator as
suggested here. Nothing works.

The times being summed are a result of IF(F2="","",TEXT(F2-E2,"[h]:mm")), I
have also taken the bracets off the h, but then loose + 24hour periods. What
does add radio buttons do?

"JE McGimpsey" wrote:

> SUM() ignores text, while the + operator will coerce its text arguments
> to numeric if it can.
>
> Try coercing your data to actual numeric values (which is how XL stores
> times - as fractional days) by copying an empty cell, selecting your
> values, choosing Edit/Paste Special, selecting the Values and Add radio
> buttons, then clicking OK. Format the result as time.
>
>
>
> In article <[email protected]>,
> AJG <[email protected]> wrote:
>
> > Is there anyone out there who knows why the SUM function won't work, but
> > manual addition will?
>

[JE McGimpsey]

By using TEXT(F2-E2,"[h]:mm"), you are creating a Text string which is
stored in your cell, not a time value. XL stores time values as
fractional days, so, for example, 3:00:00 is stored as 0.125, and
30:00:00 = 1.25.

Your formula, however, does not get converted to a number, because you
use "Text"

Better to use

=IF(F2="","",F2-E2)

and FORMAT the cell as [h]:mm.

My instructions for coercing text to numbers takes advantage of the fact
that XL will try to convert your text "time" into a real time if it's
involved in a math operation. By selecting the Add radio button after
copying an empty cell, you're effectively adding zero to each cell in
the selection. This coerces your text entries to real numbers, which you
then should format as [h]:mm.




In article <[email protected]>,
CCW <[email protected]> wrote:

> I don't understand this answer. I have a column of times, summing to zero.
> I have tried the formula, SUM(G2:G10)*24, formatting the cell [h]:mm, both
> found in the help menu. I have tried the formula with the + operator as
> suggested here. Nothing works.
>
> The times being summed are a result of IF(F2="","",TEXT(F2-E2,"[h]:mm")), I
> have also taken the bracets off the h, but then loose + 24hour periods. What
> does add radio buttons do?