Conditional Formatting every other occurence

[tahonick]

Hi all,

Background
I'm creating a sick/vacation tracker. I want the dates (and days of the week) going horizontally across the spreadsheet. It automatically sums sick and vacation this way easily.

The Problem
I have to enter these days into payroll every other Thursday, so I want to have every other Thursday highlighted red using conditional formatting, as I have it setup to change the date labels (Mon, Tues, etc.) to automatically update based on the year listed in another cell. How would I do have it automatically highlight every other Thursday?

It currently looks like this:

I want it to look like this:

Thanks in advance!

Best,
Tahonick

 

[CodeNinja]

Ok... Assuming your first day starts in column A and your day row (Mon, Tue, Wed, ETC) is on row 3,
Highlight the entire range (A3:whatever3) and use conditional formatting formula
=AND(MOD(COLUMNS($A:A),14)>7,A3="Thu")

Good luck,

CN.

 

[tahonick]

Thanks for the reply CN,

It seemed to half-work, but it is highlighting all the Thursdays.

 

[CodeNinja]

Change the formula
from =AND(MOD(COLUMNS($N:$NY),14)>7,N6="Thu")
to =AND(MOD(COLUMNS($N:N),14)>7,N6="Thu")

That $ before the second part of the columns is locking it to be constant and I'm not sure why you have N:NY

Good luck,

CN.

 

[CodeNinja]

Also of note, this will make the 2nd pair highlighted ... if you want the FIRST pair to be highlighted, you would have to change it to <8 instead of >7

 

[tahonick]

You, my friend, are no ninja. You're a wizard.

THANKS!

 

[tahonick]

I want to reiterate how much I appreciate your help.

If I might ask one more question, is there a reason it doesn't seem to work correctly when I switch the reference cell to 2017? It starts working in October but skips september for some reason.

The last thing to complete the sheet would be to get the weekend's cells grayed out automatically.

 

[CodeNinja]

Ok... sounds like 2 separate questions...

First one... I'm assuming you have some formula to change the text of cells in row 6 that refers to L3? and in L3 you have 2016...
From what I am hearing, if you change L3 from 2016 to 2017, the conditional formatting stops working... is that correct? If that is the case, I'm a bit confused, so please provide the formulas in row 6...

Second one... I think you want a conditional formatting something like =OR(M6="Sat",M6="Sun")

Good luck,

CN.

 

[tahonick]

Yeah, 2 questions. Thought I'd take advantage of you while I had you.

First one... Yes, L3 is named CalendarYear. Here's the formulas I used for those:
=TEXT(WEEKDAY(DATE(CalendarYear,9,1),1),"aaa")
=TEXT(WEEKDAY(DATE(CalendarYear,9,2),1),"aaa") etc.

Second one... I gave it a shot and it only shaded the first row. I don't expect you to fix both in one shot. I can explore google for a while and try to figure this one out unless it's an easy one for you.

Thanks again!

 

[tahonick]

I figured out the second one! I needed to lock the formula to =OR(M$6="Sat",M$6="Sun")