Does anyone know how to create a formula to sum up time sheet hours?

  • Thread starter Thread starter altie
  • Start date Start date
A

altie

I have a worksheet that has employees punch in times, punch out times
and lunch out/in times. I am trying to create a formula that calculate
the total amount of time worked.
First I want to take time in subtract it by time out, then take th
lunch do the same thing then finally take the first answer and subtrac
it by the second answer. I'm trying to make an if statement and it'
not working. The times are fomatted like 10:30 AM for ex
 
Hi Altie!

With your IN-OUT IN-OUT in columns A, B, C, and D

=(B1-A1)+(D1-C1)
Format hh:mm

If you want the answer as decimal hours as opposed to Excel time format:

=((B1-A1)+(D1-C1))*24

That's the basics and assumes that the times do not span midnight.

For further details see:

See
Chip Pearson
www.cpearson.com/excel/datetime.htm
Tons of information about working with dates and times in Excel.


--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi

When working time never exceedes midnight, Norman's formula will do. More
general formula (for time intervals up to 24 hrs, and with layout like in
Norman's example) is:
=(D1-A1+(D1<A1)) - (C1-B1)
or when the pause time can be at midnight too, then:
=(D1-A1+(D1<A1)) - (C1-B1+(C1<B1))
 
Back
Top