Need Help Please

J

jlogan21

I need some help working with times. I need to be able to add up times
worked for 12 months of the year. For example if one workd 49 hours
and 23 minutes the month of Jan and the same for Feb, how do I do this
and keep a running sum of them? I can't see to figure out how to
format it so that it does not change to time of day since it will be
over 24 hours.
Thanks.
 
R

Roger Govier

Hi
The cell holding the cumulative time needs to be formatted as [hh]:mm
Format>Cells>Number>Custom [hh]:mm
 
J

jlogan21

Ok I tried that and this is what happend. For the month of Jan. I have
total hours of 146 hours and 23 minutes. When I formatted the cell it
turned it into 3509:31?
 
R

Roger Govier

Hi
What is the formula you are using?
What does the data look like in the source cells being summed?
 
J

jlogan21

I am just using the =SUM(A1:A10). But can't seem to figure out how to
format the cells so that it will add the times correctly. I just dont
know why it is changing the numbers the way that it is.
Thanks
Jennifer
 
J

jlogan21

Each of those cells contains the total hours worked for the month. The
example I was using was that Jan. has a total number of hours worked at
146 hours and 23 minutes. I am trying to do a year to date total in
another celll, that will keep a total of all hours worked each month.
At the moment I do not have any formatting as I cant figure out how it
is supposed to be done so that it will add the hours and minutes
correctly to keep the running total. Ultimate goal is to have a total
time worked for the year.
Thanks
Jennifer
 
R

Roger Govier

Hi Jennifer

You're still not saying exactly what is in the cells A1:A10. Are there
formulae in the cells, if so what are they?
If you go to Format>Cells, what is the format set to for those cells? Is
it hh:mm, is it General, is it number?
 
J

jlogan21

There is no formula in those cells. And they are not formatted anywa
right now as I cant figure out what format to choose. Everytime
format one of the cells, it changes the amount. For example:
A1 146 hours and 23 minutes is what I need it to be. But when I try t
format it with the hh:mm one it changes it to like 3109:31 or somethin
like that. I need it to be

A1 146:23
A2 153:45

and so on. But those are hours and minutes. Then I need them al
added up to determine the total number of hours worked in a year. S
there will be 12 cells with the number of hours and minutes and then
will have a cell with the formula to add them all up. SO that cell wil
have the =SUM(A1:12) but still need it to be correct hours and minutes.
I hope that clears things up!
Thanks for your help!
Jennife
 
R

Roger Govier

Hi Jennifer

I can't think what is going on here.
On a brand new sheet, if I type 146:23 into A1, and 153:45 into A2, then
what I see is 146:23:00 and 153:45:00
If I sum these 2 cells, I see 308:31:00
If I look at the format for the cells, then XL has guessed I want hours
running over 24, and set a Custom format of [h]:mm:ss
I can get rid of the :00 to remove the seconds and just have the values
shown as hours and minutes.

This is with XL2003. Maybe earlier versions aren't so intelligent in
their guessing, but I still can't see how it would get it that wrong.
Try again on a fresh sheet and see what it does.

If you are still having problems, then feel free to mail me direct with
a copy of your sheet and I will try and see what is going on.

To email me direct, remove NOSPAM from my email address.
 
J

jlogan21

Ok now with that format it seems to work fine. I guess I did not tr
that one. Duh! Thank you so much for your help, it is greatl
appreciated!
Jennife
 
Top