I attempted to use a mod formula but was not successful. I've since deleted it. I'm looking to generate something to assist with schedule adherence.
On Sheet 1, Agent Joe Smith works 8:30a-5:00p. I would like cell B2 (next to his name on different sheet) to reflect his status depending on the time of day. For instance, if it's currently 10:05am, that falls into his first break time from 10:00-10:15. The B2 status should say "On Break" and of course if it's any time outside of his 2 15-minute breaks or his 30-minute lunch, it should display "On Calls"
The times for each agent's breaks will differ. So the actual lookup for each period will need to be based on spreadsheet 2 which will display data as:
Any ideas?
Thank you in advance
On Sheet 1, Agent Joe Smith works 8:30a-5:00p. I would like cell B2 (next to his name on different sheet) to reflect his status depending on the time of day. For instance, if it's currently 10:05am, that falls into his first break time from 10:00-10:15. The B2 status should say "On Break" and of course if it's any time outside of his 2 15-minute breaks or his 30-minute lunch, it should display "On Calls"
The times for each agent's breaks will differ. So the actual lookup for each period will need to be based on spreadsheet 2 which will display data as:
Sheet2 | A | B | C | D | E | F | G |
3 | Agent Name | Shift Start | Shift End | Break 1 | Lunch Start | Lunch End | Break 2 |
4 | Joe Smith | 8:30 | 5:00 | 10:00 | 12:15 | 12:45 | 3:15 |
Any ideas?
Thank you in advance
Last edited by a moderator: