Theneoalchemist
New Member
- Joined
- Mar 6, 2020
- Messages
- 5
- Office Version
- 365
- 2016
- Platform
- Windows
Hi Everyone, This has been stumping me for about a week now and I need assistance. My work's scheduling program produces a horrible table to track schedules and I am trying to take the information it outputs and create a printable schedule from it. I have been able to do it for most of the shifts utilizing Mod functions as well as Maxifs and Minif functions but I am running into issues regarding Overnight shifts and Overtime shifts. This stems from the fact that this output file our system generates creates multiple line items for each date which highlights non worked hours and overtime hours. So when working for the overnight shift and if the shift has overtime there can be multiple rows of data for any given date, which makes Maxifs and Minifs complicated to work with given all the parameters.
This means that if an employee works Wednesday over night, 2200 - 0900 and then comes back at 2200 for their next shift, the output file will look like so
Another issue I am running into is that the system will take a shift, lets say Friday 10pm to Saturday 6am, and split that into two line items. I tried to counteract this utilizing a OFFSET function but have ran into errors again due to how it reads the lines of data. I will post examples below.
I want to take this ( had to remove non important information but wanted to keep the columns in place
And have it look like this afterwards (This part has already been completed using a mixture of Index and Match, but the issue is getting the "##:## - ##:##" part right for Graveyard shifts.) As you can see. I am also trying to incorporate that if the line item only has half the shift (For instance, officer gets off in the morning and has the night off) as a blank value and have it shown on the night the shift starts. I have usually been able to accomplish this with adding 1 day to the Maxif's function that would display it. But again, I have been having some issues with this.)
I feel once someone assists me it will be a simple solution but I am honestly flustered at this point and have been trying to solve this for days. Any help will be greatly appreciated
This means that if an employee works Wednesday over night, 2200 - 0900 and then comes back at 2200 for their next shift, the output file will look like so
| Full Name | Work Date | Time Code | Hour Type | Start Time | End Time | Hours | |||
| Employee #1 | 08/23/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/24/2023 | WRK | REG | 00:00 | 08:00 | 8.00 | |||
| Employee #1 | 08/24/2023 | WRK | OT1.5 | 08:00 | 09:00 | 1.00 | |||
| Employee #1 | 08/24/2023 | GAP | UNPAID | 09:00 | 22:00 | 11.00 | |||
| Employee #1 | 08/24/2023 | WRK | OT1.5 | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/25/2023 | DAYCUT | UNPAID | 00:00 | 08:00 | 8.00 | |||
| Employee #1 | 08/25/2023 | WRK | REG | 00:00 | 08:00 | 8.00 |
Another issue I am running into is that the system will take a shift, lets say Friday 10pm to Saturday 6am, and split that into two line items. I tried to counteract this utilizing a OFFSET function but have ran into errors again due to how it reads the lines of data. I will post examples below.
I want to take this ( had to remove non important information but wanted to keep the columns in place
| Full Name | Work Date | Time Code | Hour Type | Start Time | End Time | Hours | |||
| Employee #1 | 08/18/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/20/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/20/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/21/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/21/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #1 | 08/21/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/21/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/22/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/22/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #1 | 08/22/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/22/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/23/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/23/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #1 | 08/23/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/23/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/24/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #1 | 08/24/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #1 | 08/24/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #1 | 08/24/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #2 | 08/19/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #2 | 08/20/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #2 | 08/21/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #2 | 08/22/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #2 | 08/23/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #3 | 08/18/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #3 | 08/21/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #3 | 08/22/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #3 | 08/23/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #3 | 08/24/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee #4 | 08/18/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/18/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #4 | 08/18/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #4 | 08/18/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/19/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/19/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #4 | 08/19/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #4 | 08/19/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/20/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/20/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #4 | 08/20/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #4 | 08/20/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/21/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/23/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #4 | 08/23/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/24/2023 | WRK | REG | 00:00 | 06:00 | 6.00 | |||
| Employee #4 | 08/24/2023 | GAP | UNPAID | 06:00 | 22:00 | 16.00 | |||
| Employee #4 | 08/24/2023 | WRK | REG | 22:00 | 00:00 | 2.00 | |||
| Employee #4 | 08/24/2023 | DAYCUT | UNPAID | 00:00 | 06:00 | 6.00 | |||
| Employee # 5 | 08/18/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee # 5 | 08/21/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee # 5 | 08/22/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee # 5 | 08/23/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee # 5 | 08/24/2023 | WRK | REG | 14:00 | 22:00 | 8.00 | |||
| Employee # 6 | 08/18/2023 | WRK | REG | 06:00 | 14:00 | 8.00 | |||
| Employee # 6 | 08/21/2023 | WRK | REG | 06:00 | 14:00 | 8.00 | |||
| Employee # 6 | 08/22/2023 | WRK | REG | 06:00 | 14:00 | 8.00 | |||
| Employee # 6 | 08/23/2023 | WRK | REG | 06:00 | 14:00 | 8.00 | |||
| Employee # 6 | 08/24/2023 | WRK | REG | 06:00 | 14:00 | 8.00 |
And have it look like this afterwards (This part has already been completed using a mixture of Index and Match, but the issue is getting the "##:## - ##:##" part right for Graveyard shifts.) As you can see. I am also trying to incorporate that if the line item only has half the shift (For instance, officer gets off in the morning and has the night off) as a blank value and have it shown on the night the shift starts. I have usually been able to accomplish this with adding 1 day to the Maxif's function that would display it. But again, I have been having some issues with this.)
| Employee Name | Friday | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday |
| 08/18/2023 | 08/19/2023 | 08/20/2023 | 08/21/2023 | 08/22/2023 | 08/23/2023 | 08/24/2023 | |
| Employee #1 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | ||
| Employee #2 | 1400-2200 | 1400 - 2200 | 1400-2200 | 1400-2200 | 1400-2200 | ||
| Employee #3 | 1400-2200 | 1400-2200 | 1400-2200 | 1400-2200 | 1400-2200 | ||
| Employee #4 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | 2200 - 0600 | ||
| Employee #5 | 1400 - 2200 | 1400 - 2200 | 1400 - 2200 | 1400 - 2200 | 1400 - 2200 | ||
| Employee #6 | 0600 - 1400 | 0600 - 1400 | 0600 - 1400 | 0600 - 1400 | 0600 - 1400 |
I feel once someone assists me it will be a simple solution but I am honestly flustered at this point and have been trying to solve this for days. Any help will be greatly appreciated
