Calendar using fill color in cells with conditional formatting
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,I
For the Red fill, my attempt has begun with the following formula: =AND((DATE(2012,$I3,I5))>=
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.
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,I
For the Red fill, my attempt has begun with the following formula: =AND((DATE(2012,$I3,I5))>=
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!