On Thursday, September 17, 2009 at 8:41:01 AM UTC-4, Jacob Skaria wrote:
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
---------------
Jacob Skaria


"Ron Rosenfeld" wrote:

On Thu, 17 Sep 2009 00:45:01 -0700, Nikhil
wrote:

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


This seems to work for referencing the current date
=DATE(YEAR(TODAY()),MONTH(TODAY()),1+7*2)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1))