Second & Third Sunday Of The Month

[David M]

Hello Again,

Another challenge has been given.....based on a date, I
have columns that display the Month, Weekday (name), Day
(number), Year, and WeekNum....I need to be able to
identify and highlight the second & third Sunday of each
month based on whatever date I enter....

The reason behind this is to quickly identify maintenance
windows for critical systems to calculate actual
availability


Can anyone help me???

Thanx

 

[David McRitchie]

You can work directly from the dates
=AND(WEEKDAY($A1)=1,DAYOFMONTH($A1)>7,DAYOFMONTH($A1)<22)

 

[Ron Rosenfeld]

Hello Again,

Another challenge has been given.....based on a date, I
have columns that display the Month, Weekday (name), Day
(number), Year, and WeekNum....I need to be able to
identify and highlight the second & third Sunday of each
month based on whatever date I enter....

The reason behind this is to quickly identify maintenance
windows for critical systems to calculate actual
availability


Can anyone help me???

Thanx

If the actual date is in one of those columns, then you could use the
conditional format and use the Formula Is:
=AND(DAY(A1)>=8,DAY(A1)<=21,WEEKDAY(A1)=1)

Substitute A1 for the cell with the date.

If you have to create the date, then for A1 substitute:

=DATE(YEAR, MONTH, DAY(number)) -- assuming DAY(number) is the date in the
month.


--ron

 

[Bernie Deitrick]

David,

With year in A1 and month number in B1, then the second Sunday is

=DATE(A1,B1,16-IF(WEEKDAY(DATE(A1,B1,1))>1,WEEKDAY(DATE(A1,B1,1)),7+WEEKDAY(
DATE(A1,B1,1))))

and the third Sunday is

=DATE(A1,B1,23-IF(WEEKDAY(DATE(A1,B1,1))>1,WEEKDAY(DATE(A1,B1,1)),7+WEEKDAY(
DATE(A1,B1,1))))

If you want to just enter a date in A1, then substitute YEAR(A1) for each
occurence of A1, and MONTH(A1) for each B1.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Ignore this: I misread your setup....

Bernie
MS Excel MVP

 

[Jason Morin]

DAYOFMONTH?

-----Original Message-----

You can work directly from the dates
=AND(WEEKDAY($A1)=1,DAYOFMONTH($A1)>7,DAYOFMONTH($A1)<22)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Another challenge has been given.....based on a date, I
have columns that display the Month, Weekday (name), Day
(number), Year, and WeekNum....I need to be able to
identify and highlight the second & third Sunday of each
month based on whatever date I enter....

The reason behind this is to quickly identify maintenance
windows for critical systems to calculate actual
availability


Can anyone help me???

Thanx

.

 

[David McRitchie]

hmmmph. Should be DAY like in Ron's code.
darn. Now I have to give at least five good replies,
maybe it's 5 1/2 good replies to make up.