Find 2nd & 4th saturday for every month

  • Thread starter Thread starter Nikhil
  • Start date Start date
N

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
 
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
 
Correction:
'fourth saturday
=FLOOR(DATE(YEAR(A1),MONTH(A1),28),7)

If this post helps click Yes
 
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
 
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",""))
 
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
 
Thanks that worked!!!

Jacob Skaria said:
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
Click to expand...
 
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
Click to expand...

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
 
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
Click to expand...

You should add a caveat that this approach works ONLY with the 1900 date
system.
--ron
 
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
 
Back
Top