Excel Conditional Formatting

[Delita]

I am trying to make a small calendar in Excel where an employee who gets one day of the week off every two weeks, inputs which week and which day. Whichever day of the week then gets highlighted in the calendar portion.

I'm not really sure what the best way to go about this would be. My current format is:

.........January
.........1 2 3
.........M T W
Name......x

where the x cell would need to be highlighted if the employee choose that particular tuesday every other week. What would be the best way to go about this or what are some things I should change?

Let me know if you need any more info.

 

[theknight571]

Ok... let me take a shot at this

Note: I'm using Excel 2007

Here's what I did:

Row 1 Beginning with Col C contains dates (ex: 1/1/2010) formatted to only show the day number (1,2,3...) Format Cells -> Custom Format -> Type: d
Row 2 Beginning with Col C contains dates (ex: 1/1/2010) formatted to only show the day (Mon, Tue, Wed...) Format Cells -> Custom Format -> Type: ddd
Row 3
- Col A contains the person's name,
- Col B contains the date of their first day off (ex:1/4/2010)
- Col C and on =IF(MOD(($B3-C$1),14)=0,"X","") --> Copy/Paste formula column to column through end of year
Row 4
- Col A contains the person's name,
- Col B contains the date of their first day off (ex:1/4/2010)
- Col C and on =IF(MOD(($B4-C$1),14)=0,"X","") --> Copy/Paste formula column to column through end of year

And so on...

Then you can highlight the calendar area and Select Conditional Formatting -> Highlight Cell Rules -> Text That Contains -> Set text to X and pick color

Basically the formula says that if their start date is 14 days exactly (MOD indicating that the number of days between the two dates divided by 14 leaves 0 remainder) put an X in the cell, which then is colored by the conditional formatting.

Let me know if I misinterpreted your request.

PM Me if you'd like to have me send you the file I created.

-TK