how to add hours and show actual hours not decimal numbers

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
 
D

David McRitchie

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
 
S

SteveW

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top