How do I generate a table on another sheet depending on input data?

I am tracking business hours of various business locations. I would like to be able to enter the businesses operating hours on the "overview sheet" and, based on those hours, have a table generated on another sheet that "autofills" based on the date entered on the "overview sheet". For example:


I would enter the operating hours of the business on the "overview sheet" and it would look something like this:

Day	Open	Close
Monday	8:00 AM	7:00 PM
Tuesday	8:00 AM	7:00 PM
Wednesday	8:00 AM	7:00 PM
Thursday	8:00 AM	7:00 PM
Friday	8:00 AM	10:00 PM
Saturday	9:00 AM	7:00 PM
Sunday	0:00	0:00

Then, on another sheet called "Time Slot Rankings", I want to automatically generate a table for each of the days that looks something like:

Monday	Ranking
8AM-9AM
9AM-10AM
10AM-11AM
11AM-12PM
12PM-1PM
1PM-2PM
2PM-3PM
3PM-4PM
4PM-5PM
5PM-6PM
6PM-7PM

I want a table for each day created. I would then manually enter the data for the "Ranking" column. The issue is that I have numerous location I want to do this for and all with varying hours of operation. It is very tedious to manually create the tables.

Any help would be greatly appreciated!

Here is an example:

excel_example.xlsx

Thanks for your reply.

It appears that the data you used in your formulas on the "new sheet" was not data that originally existed on the "overview sheet". What I am looking for is using the "Hours of Operation" data on the overview sheet to generate the components of the "Time Slot Ranking" table.

I think I understand what you are saying, but I am trying to avoid creating that table manually. If I create it manually, I have no need to use formulas to create another table on another sheet.

This is an update to what I have done so far. I can now manipulate the hours of operation on the "Overview" sheet and it will populate on the "Time Slot Rankings" sheet. One issue I am having is the "#VALUE!" in unused fields. Anyone know how I can get rid of that? I thought using the ISBLANK() function inside my OR() function would return "", but that isn't working. I want the formulas on the 'Time Slot Rankings" sheet to run from cell rows 2 to 24, that will account for all possible time slots if a business is open for 24 hours.

excel_example.xlsx

Just updating that I have gotten the workbook to perform as desired by enclosing all of my formulas with IFERROR(original formula), "").