Developing Production Calendar Spreadsheet - Help!

Hello everyone,

I work for a manufactuing company, where the production shift hours at times can be variable. What I have been tasked with is to create a calendar in Excel for Production hours. What I am envisioning is to have an actual Worksheet that looks like a real calendar (all months and days on one Worksheet). Then, I would like to have another Worksheet table where Production hour ranges are defined, and then map each day on the calendar Worksheet to each range type.

For example, we may have two ranges: 1) Normal Production Hours = 6:00am - 3:00pm and 2) Overtime Hours = 6:00am - 4:00pm. The 6:00am start time would be in one field and the 3:00pm would be in another field in the row (for example). These ranges would be listed on a separate worksheet. Then, on the calendar worksheet, I would like the user to be able to define each day where there is a change from the Normal Production Hours. Maybe use a different color background for those days, as an example.

An example of this would be, January 17th overtime hours were worked (6:00am - 4:00pm), but every other day in January would be Normal Production Hours (default). January 17th would then be highlighted in a Red background color. Then, a program I would develop (or maybe a macro) would be able to calculate each day's total work time. Each day's work time will be used for another calculation to determine figures like 'Average time of production per vehicle'. It will see the 'Red' background color for Jan 17th and automatically know that it was not a default production hour range time, but the 2) Overtime Hours range time.
I
My question is, since my background is in programming, but I am not an advanced user in Excel...what functionality in Excel could help me accomplish this? Is the background color change a good idea, or is there another way of developing this spreadsheet altogether? I would like to be able to pull the default time range (Normal Production Hours) from the spreadsheet, and then, iterate through the calendar to find the Overtime Hours range. So, in my example above, my program would query the spreadsheet and see that in Jaunary, all production days were Normal Production days, except January 17th, where that day = Overtime Hours (6:00 - 4:00pm).

Thank you in advance!!

I'm new here...so should I have posted this to the Visual Basic forum?