Help for a newbie trying to add hours and minutes :)

[Bugjam1999]

Hello all,

the wonders of the internet may just solve this problem which has my
office stumped...

from a entry gate system i have a huge excel spreadsheet with totals
per day of hours on site per employee... essentially as below on a much
larger scale.

dave I 01/01 I 7:15
dave I 02/07 I 8:20
dave I 03/07 I 8:20
dave I 04/01 I 8:30
dave I 05/01 I 8:20

the capital i's are inserted just to show where the next cell, so for
the mini data above - employee dave (cell one) was on site for 7 hours
and 15 minutes (cell two) on 01/01 (cell three), then for 8 hours and
20 minutes on 02/01.

"all" i want to do is add up the amount of time he was on site for the
entire week, in this case 40 hours and 45 minutes and display it in a
similar format in the summing cell, ie 40:45

i've tried summing up the data to the cell below, have formatted
everything in sight using the custom settings to hh:mm and have also
copied the list to another column using "paste special" to only
transfeer the values in case the program running the entry system left
some forumla hiding in the cells... the best i get is 00:00.

i'm out of ideas. I'm sure this is very simple, but it's got me!
surely microsoft didn't release excel without the ability to add times
up?

many thanks,

S.

 

[Newmoon]

hi, S.
you simply need to use the SUM() formula.

let´s say your values are stored in cells A1 to A5

Use this in the cell in which you want to display the Total:

=SUM(A1;A2;A3;A4;A5)

greez,
Newmoo

 

[Guest]

The correct format for displaying cumulative time that may exceed 24 hours is
"[h]:mm" or [hh]:mm.

The fact that you get 00:00 instead of 16:45 from a format of "hh:mm"
suggests that you have not entered these as Excel times. What do you see in
the formula bar when you select the cell that displays as 7:15? An excel
time would display in the formula bar as
7:15:00 AM
What happens if you format that same cell as General? An Excel time would
then display as 0.302083333333333, which is =(7+15/60)/24

Jerry

 

[Bugjam1999]

thanks for the reply... but that doesn't work. Sum just produces th
result 00:00. Thats what got us stumped, all of us have used the su
function loads of times before without difficulty... but it doesn'
work this time

next suggestion please?

thanks..

 

[Bob Phillips]

Sounds like the cells are text, not proper time

Instead of Sum, try this

=SUMPRODUCT(--(A1:A10))

and format as [hh]:mm:ss

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bugjam1999]

Jerry - thanks for the reply

The first cell displays 7:15 in number or general format. I guess thi
means that excel thinks it's text?

surely i don't want it to say 7:15:00 AM because that suggests th
time...? dave spent 7 hours and 15 minutes on site on the first day
it's a cumulative value not a signing in time.

sounds like i need to change the format to make excel recognise th
values as numbers, more specifically cumulative times of hours an
minutes in order to be able to add them. There must be a way to d
this without changing each cell manually - theres 4000+ lines of data
changing each cell individually isn't an option!

Tried changing the column to format [hh]:mm and no difference....

Regards,

 

[Bugjam1999]

Now we're cooking with gas

that works fine... thank you very much

just for interests sake - sum product asks excel to make a number out
of a text cell? and the relevance of the two dashes -- and double
brackets?

thanks guys, much appreciated...

S.

 

[Newmoon]

Rehi!
something i really did not recognize is that excel changes the datatype
of the cell unasked just when entering something like 7:15.
it is being converted to a number with the category CUSTOM and the
format hh:mm:ss.

however, the SUM-function works correctly on my machine (office2003
installed)
so bob´s assumption that your cells are formatted as text seems to be
the only explainable reason...

...the longer i work with excel the more scatterbrained i become



bye,
newmoon

 

[Bob Phillips]

No, SP just does the summing in this case, the -- coerces the text value to
a number.

--

HTH

RP
(remove nothere from the email address if mailing direct)