Calendar using fill color in cells with conditional formatting

on
We are utilizing a form of calendar to schedule vacation time. There are four dates being used: Begin Travel Date (Begin leaving from work site), Vacation Start Date (arrival to destination), Vacation End Date (leaving destination), and End Travel Date (arrival back at work site). These dates are listed in cells C5, D5 E5 and F5 respectively. The calender extends to the right and has the entire year listed in one row. Each day of the month is represented by the number of that day in one cell, and the row above lists the respective month as shown in the attached excel spreadsheet. Vacation-Tracker.xlsx

All the days have a color fill of green in their cell. The goal is to use conditional formating that will use a color fill of orange for the days that begin with the Begin Travel Date up to the Vacation Start Date and from  the Vacation End Date through the End Travel Date (not including Vacation dates). And a color fill of red for the days between and including the Vacation Star and Vacation End Dates.

For the Orange fill, my attempt has begun with the following formula: =OR((AND(((DATE(2012,I\$3,I5))>=\$C5),((DATE(2012,I\$3,I5))<\$D5))),(AND(((DATE(2012,I\$3,I5))>\$E5),((DATE(2012,I\$3,I5))<=\$F5))))

For the Red fill, my attempt has begun with the following formula: =AND((DATE(2012,\$I3,I5))>=\$D\$5,(DATE(2012,\$I3,I5)<=\$E\$5))

The formulas are not working out as desired. I am hoping to find a way to populate the month as in cell (I-AM columns are combined) I-AM4, which reads January, as a number. Also, I am having trouble using fill series to populate the rest of the cells accurately, once the conditional formatting has been input to one cell.

I summary, any help on getting the conditional formatting formulas to be more accurate would be appreciated. Also help is needed on finding a way to populate the month in the formulas as  a number to avoid retyping the formulas for each month. And lastly help on using fill series to populate the rest of the cells with the conditional formatting so as to avoid retyping it for each cell.

Thanks in advance for any assitance on this matter.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The problem becomes enormously easier if you use Custom formatting for your dates. For example, I put 1/1/12 in I4, 2/1/12 in AO4, etc. and then used the Custom format mmmm to make the cell display the name of the month. I put 1/1/12 in cell I5 and used Custom format d to display the day of the month.

Your Conditional Formatting formulas then become:
=OR(AND(I5>=\$C5,I5<\$D5),AND(I5>\$E5,I5<=\$F5))           'orange, travel before or after vacation
=AND(I5>=\$D5,I5<=\$E5)              'red, the vacation itself
Vacation-Tracker-Q27675605.xlsx

Commented:
Byundt,

Changing the formatting of the cells as you suggested really did simplify so many aspects of the problem. Perfect solution. Thank you for your expertise and knowledge; it is well appreciated!

Do more with