US Federal Floating Holidays

I have a calendar wizard that will present the calendar for the year that is entered. But I would like to have it highlight in blue those floating federal holidays (MLK Day-3rd Mon. in Jan, Presidents Day-3rd Mon in Feb, Memerial Day-Last Mon. in May, Labor Day-1st Mon. in Sept., Columbus Day, 2nd Mon. in Oct and Thanksgiving Day-4th Thur. in November). I know how to make it highlight the fixed holidays like Christmas and New Years.

Thanks

You could use Conditional Formatting.

For MLK which is the Third Monday in January, the holiday will either be in Cell D18 or D19

By check on the contents of the First Monday cell which is cell D16 you can determine which of the two target cells will change color.

In Cell D18 add the conditional formatting formula
=IF($D$16>=1,TRUE,FALSE)
In Cell D19 add the conditional formatting formula
=IF($D$16<1,TRUE,FALSE)

You should be able to use the same logic for all of the Monday holidays.

But both D18 and D19 already have formula in them.

You can still use Conditional Formatting:

I’m using Excel 2000 so these directions may be off a bit:

1) Select your cell, we’ll use D18

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF($D$16>=1,TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

Now repeat the same procedure for Cell D19 and enter the formula =IF($D$16<1,TRUE,FALSE)

For Presidents Day, 3rd Monday in February
In Cell M18 the formula would be =IF($M$16>=1,TRUE,FALSE)
In Cell M19 the formula would be =IF($M$16<1,TRUE,FALSE)

Thanks Xenixman, It works! I also got it to work for Memorial Day in September. However, I'm having trouble with Columbus Day and Thanksgiving Day. Any idea? For Columbus I formatted D44 and for Thanksgiving I formatted P46.

For Columbus Day you would Conditionally Format
Cell D44 =IF($D$43>=1,TRUE,FALSE)
Cell D45 =IF($D$43<1,TRUE,FALSE)

For Thanksgiving
Cell P46 =IF($P$43>=1,TRUE,FALSE)
Cell P47 =IF($P$43<1,TRUE,FALSE)

Thanks again! Those works great too. Now if you can just help me with last and most difficult one, Memorial Day, which is the last day of May.

The logic for Memorial Day is the same as the others, except your not checking the cell for the First Monday, but the last possible cell that can be Monday.

Cell M29 =IF($M$30<1,TRUE,FALSE)
Cell M30 =IF($M$30>1,TRUE,FALSE)

You can actually use Cell Value Is selection in Conditional Formatting in cell M30.

If Cell M30 is greater than 0 will also work.