Calculating Hours and Minutes in Excel

[Janet]

I am doing payroll hours in an excel spreadsheet. Is
there a way to enter the time in and time out and have a
formula calculate how much time it comes to. For
example, I have an employee that started at 8:55am and
left at 10:40am. Is there a formula that calculates how
much time that comes too?

Thanks very much, Janet

 

[Frank Kabel]

Hi Janet
If A1 stores the time the employee started working and B1 the check-out
time simply subtract both values:
=B1-A1
If you want to use this number to calcualte his wage, multplky the
result with 24:
=(B1-A1)*24*hourly rate

for more information have a look at
http://www.cpearson.com/excel/datetime.htm

HTH
Frank

 

[kimt]

I had a similar situation and developed a time sheet which uses severa
cells to convert the hour:minutes to hour.tenth of hour. For th
purposes of saving space, her is a brief descripiton. I enter the tim
as 730 (for 7:30) to save time. the following steps take place usin
one cell each (I'm sure someone could reduce this to a smaller amount.

cell A1) "=730"
cell A10) " =A1/100" returns 7.3
cell A11) "=trunc(A10,0)" returns 7
cell A12) "=A10-A11" returns 0.3
cell A13) "=A12*1.66667" returns 0.5" (converts minutes to 1/10's)
cell A14) "=A11+A13" returns 7.5

Next enter the time they went to lunch or went off shift (this is th
way I did it so that I would not have to use a 24 hr clock). Lets us
5:30, I enter "530"

cell A2) enter "522"
cell A15) "=if(A2<A1,(A2+12),A2) returns 1722 (this works on 99.
percent of entrys, the exception is if an employee works throught noo
and midnight, you'll have to adjust that (to do it automaticall
requires another 10 or so formulas))
cell A16) "=A15/100" returns 17.22
cell A17) "=trunc(A16,0) returns 17
cell A18) "=A16-A17" returns 0.22
cell A19) "=A18*1.66667" returns 0.36 (minutes to tenths)
cell A20) "=A18+A17" returns 17.36

cell A3) "=A20-A14" returns 9.86 which is the total time worked state
in hours and tenths.

I enter "Start Shift, Start Lunch, End Lunch, End Shift" which require
me to do the calculation cells in four sets...good luck