time functions

  • Thread starter Thread starter tbean
  • Start date Start date
T

tbean

Hello,
I am relatively new to Excel and I am trying to do a timesheet tha
keeps track of hours I have worked. The problem is that the spreadshee
includes times but also total hours. The two aren't really compatibl
when doing calculations. Our normal working hours per month are 14
hours. I put in what time I start and what time I finish and i
calculates how many hours I have worked (but it is still in tim
format-- e.g. 9-17:00 ends up being 8 hours on my time sheet but i
shows up as 8:00). When I do an autosum of all the total times I hav
worked during the month, the figure is wrong due to it being in tim
format (8:00 instead of 8). Is there some workaround or solution t
working with multiple formats (time AND general numbers)?

Sorry if this is confusing!
Tbea
 
Hi
just format your result cell with the custom format [h]:mm to prevent
the rollover after 24 hours. If you want to calculate your wage
multiply the Excel time with 24.
e.g. Time * 24 *your_wage_per_hour.

For more information about time/date have a look at
http://www.cpearson.com/excel/datetime.htm
 
Hi

Format the cell with autosum, (and maybe worked hours column too) as Custom
"[h]:mm". This pervents rollover at 24 hours. Or you convert hours to
numbers - replace the sum formula with something like
=SUM(C2:C33)*24
and format as number

Another thing to consider - whenever the working time includes midnight, you
formula doesn't work properly. So it will be safer to use for calculating
working time in a shift the formula like
=B2-A2 + (B2<A2)
which works fine with shifts <24h
(Start time is in column A and end time in column B in my example)
 
Back
Top