Find 2nd & 4th saturday for every month

[Nikhil]

I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm

heading "Nth day of month and year"

 

[Jacob Skaria]

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


If this post helps click Yes

 

[Jacob Skaria]

Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

If this post helps click Yes

 

[Nikhil]

Hi

Thanks for the help but let me clarify...

I have the list of dates in Col A (starting from one date and stretching for
around 3 months). In Col B, i want the cell value to be either blank or
indicate "Second Saturday" or "Fourth Saturday"

Hope this clarifies my reqt.

Thnks

Nikhil

 

[Ms-Exl-Learner]

Try this...

=IF(FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)=A1,"FIRST
SATURDAY",IF(FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)=A1,"SECOND SATURDAY",""))

 

[Jacob Skaria]

Try the below

=IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7),"Second
Saturday",IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7),"Fourth Saturday",""))

If this post helps click Yes

 

[Nikhil]

Thanks that worked!!!

Try the below

=IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7),"Second
Saturday",IF(A1=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7),"Fourth Saturday",""))

If this post helps click Yes

 

[Ron Rosenfeld]

I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

Please note that Jacob's formula is dependent on your Date system being the
1900 date system. Some machines, and the default for the Mac's, will use the
1904 date system.

For formulas which will work properly regardless of the date system being used,
try:

=IF(A1=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+1)+15,"2nd Saturday",
IF(A1=A1-DAY(A1)-WEEKDAY(A1-DAY(A1)+1)+29,"4th Saturday",""))

or

=IF(WEEKDAY(A1)<>7,"",IF(AND(DAY(A1)>7,DAY(A1)<15),
"2nd Saturday",IF(AND(DAY(A1)>21,DAY(A1)<29),"4th Saturday","")))

--ron

 

[Ron Rosenfeld]

With date in A1

'second saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),14),7)

OR (n denotes the instance)
=FLOOR(DATE(YEAR(A1),MONTH(A1),n*7),7)


If this post helps click Yes

You should add a caveat that this approach works ONLY with the 1900 date
system.
--ron

 

[Jacob Skaria]

Thanks Ron for pointing that out

Nikhil, one more ..

=DATE(YEAR(A1),MONTH(A1),1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))
=DATE(YEAR(A1),MONTH(A1),1+7*4)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))

If this post helps click Yes