Timesheet and IF conditions

**Kwarren** · 10-23-2009, 10:28 AM

I have timesheet problem I need help with. I’ve attached a simplified example of my timesheet with five columns: A=Time In, B=Lunch/Other Out, C=Lunch/Other In, D=Time Out, E=Total Hours.
Factoring in the B and C columns is what has me stumped. Here are the conditions I need to have met:
If C-B is 30 minutes or less, ignore the value (do NOT factor it into the equation)
If C-B is greater than 30 minutes, subtract the additional minutes from the total.

Thanks in advance for your help!

Kirk

**darkyam** · 10-23-2009, 10:40 AM

Try =(D2-A2)-MAX(0,(C2-B2-1/48))

**Kwarren** · 10-23-2009, 10:48 AM

That’s it!!!
Can you give a brief explanation of how/why this equation works?

Thanks so much!

**darkyam** · 10-23-2009, 10:52 AM

Since Excel stores each day as 1, 1/48 is the equivalent of half an hour. Instead of that, you could use TIME(0,30,0) if it's easier. So basically, it's just taking half an hour off of each lunch time and then comparing that to 0, and then taking the larger of the two.