Highlighting Every Third Friday

[sslack]

Hi Everyone,

I have a list of dates (for example 19-Nov-82, 22-Nov-82, etc.) I want
to highlight every third friday of the month. Is this possible?

For example I want to highlight

Friday 19-Nov-82
Friday 17-Dec-82
.......
.......
.......

Thanks in advance for the help!

 

[Frank Kabel]

Hi
- select for example A1:A100
- goto 'Format - conditional Format
- Enter the following formula:
=A1=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6)
)
- choose your format

For an addin which creates such conditional formulas (But uses
worksheet events/VBA) also see:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Myrna Larson]

In addition to Frank's formula here's another one that should work with
conditional formatting. Friday is weekday 6, the 3rd week of the month
includes days 15 through the 21.

I assume the dates are in column A, and A1 is the active cell:

=AND(WEEKDAY(A1)=6,DAY(A1)>=15,DAY(A1)<=21)

 

[Frank Kabel]

Hi Myrna
nice one!

P.S.: have to admit that I double-checked with pen and paper as
counting is a little bit difficult at this time )

 

[Bob Phillips]

That is an interesting variation that I don't think I have seen before. I
think it should go in CFPlus, it is simpler than the one there.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Myrna Larson]

P.S.: have to admit that I double-checked with pen and paper as

counting is a little bit difficult at this time )

LOL. I expect the the scepticism comes from the statement "the 3rd week of the
month is from the 15th through the 21st".

The week I'm talking about isn't a week that necessarily begins on a Sunday or
Monday, but rather the week that begins on the 1st of the month. It could
start on any day of the week.

 

[Frank Kabel]

LOL. I expect the the scepticism comes from the statement "the 3rd
week of the month is from the 15th through the 21st".

The week I'm talking about isn't a week that necessarily begins on a
Sunday or Monday, but rather the week that begins on the 1st of the
month. It could start on any day of the week.

No not exactly. Just wasn't so sure if this really covers all cases
(but it does - of course). And as Bob stated we may change the formula
creation in our addin...
Frank

 

[Myrna Larson]

I think it has to. Regardless of the day you start, the first 7 days of the
month (1st-7th) includes the first occurrence of any particular weekday;
8th-14th must include the 2nd occurrence, 15th-21st must include the 3rd,
22nd-28th must include the 4th, 29th to end of month includes the 5th.

If W is the weekday (1-7, Sun = 1) and N is the occurrence of interest, the
more general formula is

=AND(WEEKDAY(A1)=W,DAY(A1)>(N-1)*7,DAY(A1)<=N*7)

 

[Frank Kabel]

Hi Myrna
already build in this way for the next release

Frank

 

[Jim May]

Frank, do you mind sharing the logic behind 1+7*3 and 8-6.
This type of thing goes right over my head,,, &^%*&^@#.
TIA

 

[Frank Kabel]

Hi Jim
sure:
1. 1+7*3
7 days a week multiplied with 3 (the third occurence)

2. 8-6:
subtract the weekday of the searched day (Friday = 6, sunday =1) from 8

 

[Bob Phillips]

Jim,

This is a formula we use in CFPlus. You could use 22 and 2, but we left it
this way to help people figure it out more easily.

--

HTH

RP
(remove nothere from the email address if mailing direct)