Excel Timesheet Formula

  • Thread starter Thread starter ecrystal
  • Start date Start date
E

ecrystal

I have a timesheet and I need it to calculate the time
between Start Work and TimeOut1, between TimeIn1 and
TimeOut2, and between TimeIn2 and End Work. Sometimes
TimeOut2/TimeIn2 are blank fields, but if they have time
entered in them, I need them to calculate. Right now they
won't. The IF statement I wrote bombs when I try and
calculate the difference between TimeOut2 and TimeIn1.

Here's a sample:
=((D6<C6)+D6-C6+IF(D7="",,(D7<E6)+D7-E6)+(F6<E7)+(F6-E7)). C6 is Start
Work, D6 is TimeOut1, E6 is TimeIn1, D7 is TimeOut2, E7 is TimeIn2, F6
is End Work. I've attached a copy of the timesheet.

Can anyone help? I'd greatly appreciate it. Thanks in
advance.

Attachment filename: crystal time sheet.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=390316
 
Hi,

So, if you want to calculate the total number of hours worked during the day:

=IF(OR(D6="",E6=""),MOD(F6-C6,1),MOD(D6-C6,1)+MOD(F6-E6,1))

Formula can be read:
When either TimeOut or TimeIn is omitted, you base your calc solely on StartWork
and EndWork.
Otherwise, calculate time between (Start and TimeOut) + (TimeIn and End)

Regards,

Daniel M.
 
Back
Top