Excel 2007 : formula with constraints

**devilslvr** · 08-26-2011, 09:56 PM

I have a spreadsheet for a work schedule.
Cell C10 - time in, eg 9:30AM
Cell D10 - time out, eg 6:30 PM
Cell E10 calculates the hours of 9

I need F10 to return a number based on the following.

If someone works more than 7 hours they need to take a one hour lunch, if they work less than 7 but more than 4 hours, they need to take a 30 minute lunch, if they work a shift that is less than 4 hours they don't get a "lunch".

So I need F10 to return to me the hours that the employee will be paid. The value of E10 less the "lunch" time, if any.

Any help is Greatly appreciated!!!

Thank you,

DevilsLvr

**jeffreybrown** · 08-26-2011, 10:23 PM

Hello & Welcome to the Board,

This seems a little messy, but it seems to work...

=IF(MOD(D10-C10,1)*24>7,MOD(D10-C10,1)*24-TIME(1,0,0)*24,IF(AND(MOD(D10-C10,1)*24>4,MOD(D10-C10,1)*24<7),MOD(D10-C10,1)*24-TIME(0,30,0)*24,MOD(D10-C10,1)*24))

**abousetta** · 08-26-2011, 10:29 PM

Hello and welcome to the forum,

Maybe look at the attached workbook and tell me if this is what you are looking for.

abousetta

**jeffreybrown** · 08-27-2011, 11:27 AM

Hi abousetta,

One observation...What if they work past midnight?

Example:
C10 = 9:30 AM
D10 = 6:30 AM
Result is 21 hours

So a minor update...instead of...

=(D10-C10)*24 which produces -3 with the above variables

Change to...

=MOD(D10-C10,1)*24 which produces 21 with the above variables

Just a thought. I know the OPs example does not cross midnight, but could be the next question.

**abousetta** · 08-27-2011, 12:10 PM

Hi Jeff,

You are right... I sort of stayed away from this issue intentionally since the original example didn't show this as a possibility. But the formula should cover all the bases for it to be complete.

abousetta