Second & Third Sunday Of The Month

  • Thread starter Thread starter David M
  • Start date Start date
D

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
 
You can work directly from the dates
=AND(WEEKDAY($A1)=1,DAYOFMONTH($A1)>7,DAYOFMONTH($A1)<22)
 
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
Click to expand...

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
 
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
 
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.
 
Back
Top