how to add hours and show actual hours not decimal numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to put together an employee work schedule, showing actual hours
for punch in and out times. How do I set it up to add actual hours not
decimal hours. Example: 11 hours is 0.46, if I change it to military time it
will show 11 hours for the day but the total for the week is still all of the
decimals added up. This is probably simple to do but I am too new with this.
Any help would be greatly appreciated, Thanks
 
B2: 23:00 start 11 PM
C2 07:00 end 7 AM
D2: =C2-B2+(C2<B2)
format as time h:mm, or

E2: =24 * (C2-B2+(C2<B2)) format a number for hours 0.0

Time is a fraction of a day, so multiply by 24 (hours) the fraction
of a day to get hours.

The (C2<B2) is a comparion it adds 0 or 1, the 1 is one day
which is equivalent to 24 hours.

More information on date and time
http://www.mvps.org/dmcritchie/excel/datetime.htm
 
Just for testing purpose

Open a new workbook and enter times in the cells and add them

ie A1 11:00
B1 19:00
C1 = B1-A1

You'll find that they display as HH:MM, no decimals in sight

So back to your workbooks, format the cells used as Time

Excel is pretty good at etting the initial format right and handles
calculations the same

The simple formula =B1-A1 can be improved to handle night workers
ie 22:00 to 07:00, here C1 would be =IF(B1>A1, B1-A1, 1+B1-A1)

The 1 is 1 whole day, ie 24 hours

Steve
 
Back
Top