Need Formula for Timesheet

Hi all hope you're doing well.

I would like to create a weekly staff schedule/shift coverage. Please take a look at the attachment.

I need a formula in every cell that can tell me whether the agent is supposed to be on shift or not. In the sheet I have attempted to highlight the agent who is on shift to green and red when they're offline, but it doesn't go according to the specific which I'm quite lost on how I should extend the formula. The idea of the file is to import the data in the dump sheet and the schedule will come up automatically.

Thank you in advance!

Thank you!

Lets say for cell 11 for example and this is the formula:

=IF(C$9>=VLOOKUP($A11,Dump!$A:$D,3,0),(C$9<=VLOOKUP($A11,Dump!$A:$D,4,0)))

It works for the first table (26/02/2024) but not the second table even if I aligned the cell value accordingly

=IF(C$41>=VLOOKUP($A43,Dump!$A:$D,3,0),(C$41<=VLOOKUP($A43,Dump!$A:$D,4,0)))

I'm guessing this is due to the lack of referencing to the agents and dates in each of the tables, and I'm at a loss at how to sneak another condition into the formula.

I'm trying to create a timesheet that will auto-populate the values of True and False in each of the time slots in 2.26 - 3.3.2024 tab, which will ultimately tell me if an agent is supposed to be on shift or not based on the date and time of day.

C11 for example returns as False because agent 1 shift is 9:00 - 17:30 based on the Dump tab.

The 26/02/2024 table works because it is based on the first values from the dump, but not for the second one. After all, the formula does not have criteria for specific dates i.e. row B in Dump and I'm hoping to correct it as I would like to include more tables in the tab.

Reattaching since the first one isn't working