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.


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile: http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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,
Newmoon


--
Newmoon
------------------------------------------------------------------------
Newmoon's Profile: http://www.excelforum.com/member.php...o&userid=26172
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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" wrote:


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.


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile: http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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

next suggestion please?

thanks...


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile: http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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

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

next suggestion please?

thanks...


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile:
http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

Jerry - thanks for the reply

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

surely i don't want it to say 7:15:00 AM because that suggests the
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 the
values as numbers, more specifically cumulative times of hours and
minutes in order to be able to add them. There must be a way to do
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, S


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile: http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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.


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile: http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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


--
Newmoon
------------------------------------------------------------------------
Newmoon's Profile: http://www.excelforum.com/member.php...o&userid=26172
View this thread: http://www.excelforum.com/showthread...hreadid=394908

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)


"Bugjam1999" wrote
in message ...

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.


--
Bugjam1999
------------------------------------------------------------------------
Bugjam1999's Profile:
http://www.excelforum.com/member.php...o&userid=26178
View this thread: http://www.excelforum.com/showthread...hreadid=394908