Help with formula to auto create break times

**avidcat** · 01-15-2012, 02:54 PM

This is a complicated formula, at least not something I can do.

There is an draft attached to this email.

Rules:
First rule is that no breaks can overlap.
1st Break is 40 Mins.
2nd Break is 20 Mins.
1st Break is at least 2 Hours After Time-In.
2nd Break is at least 2 Hours Before from Time-Out.
Each Employee might have a unavailable time for breaks.

Not sure if it is possbile to create a formula like this, or I might need an Excel Wizard to do this.

thanks for any help,

Nick

**Richard Buttrey** · 01-15-2012, 03:18 PM

Hi Nick,

You need to add the results you expect in columns C&D since it's not obvious from your post exactly what you require. You should also add notes explaining how you arrive at the results.

For instance when you say no breaks should overlap do you mean for each employee only or across all employees? In which case who takes precedence.
Using terms like 'at least' is no help when constructing a formula since it's open to interpretation.
Exactly how are the unavailable times to be used? Do you mean a break can't start within the unavailable time. Can they start a break before the unavailable time but which runs into the unavailable time?

Do employees work past midnight, i.e. is the time out less than the start time but on the next day?

**avidcat** · 01-15-2012, 04:01 PM

Thanks for the input, I placed an updated example to this post.

I hope I was able to describe it properly, easy to see it in my head and when it comes to putting it down is another thing.

Thanks,
Nick

**Richard Buttrey** · 01-15-2012, 04:38 PM

Hi,

Do the following formulae do what you want.
C3 and copied down

Please Login or Register  to view this content.

D3 and copied down

Please Login or Register  to view this content.

**avidcat** · 01-15-2012, 05:08 PM

Thank you,

I test out the code but there are conflicts, break times overlaping and I tested the unavailble times with a 1:00-3:00 PM time and it scheduled them at 1:40.

I attached an updated sample with the code, you can see how things conflict with each other.

Thank you for your help.
Nick