I'm trying to make an 'hours worked' spreadsheet. I set the cells to time and can add and subtract hours. IE 1600to 2000 = 4 hours. I can then total them up for the week. However, when I try to set my weekly hours IE 37 (lucky Me) so I can either add or subtract the totals it all goes badly wrong. I can see that the totals I am getting are in a 24 hour clock and that 37 does,nt fit. Is there a solution to this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Hours Formula
- Thread starter Drol
- Start date
You are doing everything right, I believe. All you need to do is:
Activate the cell where the total is. Go to Format, Cells, Number tab. Cick on time then click on Custom. Under the box titled "Type:" change the contents to read
[hh]:mm
those are the sqare brackets.
HTH
Indu
Activate the cell where the total is. Go to Format, Cells, Number tab. Cick on time then click on Custom. Under the box titled "Type:" change the contents to read
[hh]:mm
those are the sqare brackets.
HTH
Indu
And as a bit of extra information, the [ ] brackets around ANY time format will allow you to view morew than the intrinsic maximum value (ie 60 for ss and mm, 24 for h)
eg
if you have a time period of 36 hours that you format as hh, you will see 12 (difference between total hours and 24
format as [hh] and you will see 36
Be careful though as because it is going over the intrinsic maximum, the next hieracical level will already be included ie 36 hrs = 1 day and 12 hours so you cannot use d [hh] as you would get a nonsensical answer
HTH
Geoff
eg
if you have a time period of 36 hours that you format as hh, you will see 12 (difference between total hours and 24
format as [hh] and you will see 36
Be careful though as because it is going over the intrinsic maximum, the next hieracical level will already be included ie 36 hrs = 1 day and 12 hours so you cannot use d [hh] as you would get a nonsensical answer
HTH
Geoff
- Status
