Employee Scheduling-Time Constraint Formula Problem.

defensorfedei · Jul 2, 2012

I am a somewhat green excel user who has been charged with the task of trying to create an employee scheduling framework within excel for a non-profit retail establishment that employees roughly 100+ people. We have looked at several commercial scheduling software companies, and have not found a product to suite all of our needs.

I am trying to make formulas that I can use with conditional formatting, to draw attention to location constraint violations and scheduled time discrepancies. Much to my surprise, I have had many successes, but I fear I have hit a stumbling block this time.

Here is the problem I face:

I must schedule several employees a day to be a store greeter for a period of 1 hour. I also need to schedule breaks and lunches. I tried to make a formula that would flag the time value entered into a 'break cell' if it was equal to or less than 1 hour from the 'greeter cell' value I had entered. For example, If I scheduled someone to greet at 2:00:00 PM, I would want the value I added into the "break cell" to turn red if I entered 2:00:00 PM, 2:01:00 PM, 2:40:00 PM, etc...up until I entered 3:00:00 PM, which is 1 hour after the scheduled greeting time.

I achieved some success with the following formula:

J3=Break cell G3=Greeting cell

Code:

=IF($J$3-$G$3>= 0.99/24,"OK","NOT OK")

This achieves the desired function, however, it also reads "NOT OK" for all AM hours proceeding up to the value of G3, as well as 12:00:00 PM up to 3:00:00 PM, assuming that G3=2:00:00 PM.

I know my explanation is probably a bit long winded, and confusing, but does anyone have an idea of where I went wrong and how I could fix this? Any help would be greatly appreciated.

defensorfedei · Jul 4, 2012

Bump. If anybody could offer any advise at all, it would really be appreciated. I would be glad to clarify any point I presented earlier if it would help.

defensorfedei · Jul 6, 2012

Well, I solved my own problem. Not sure why I bothered to post here since I received zero comments or replies, but just in case this helps anyone else.

Code:

=IF(AND(J3-G3>=0/24,J3-G3<=0.99/24), "NOT OK", "OK")

I then used this to create conditional formatting for the Greeting cell, so that if the scheduled time is equal to the employee's break time, and up to 59 minutes more (resulting in a conflict), an error flag will appear. Any other times that do not result in a conflict, are not demarcated.

Employee Scheduling-Time Constraint Formula Problem.

defensorfedei

New Member

Excel Facts

defensorfedei

New Member

defensorfedei

New Member

Similar threads

Forum statistics

Share this page

Employee Scheduling-Time Constraint Formula Problem.

defensorfedei

New Member

Excel Facts

defensorfedei

New Member

defensorfedei

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock