Date function: 1st, 3rd and 5th Mondays of a month

[vvaidya]

Hello:

I wanted the value of a cell to change (either "yes" or "No") based on
the following date conditions:

Return "Yes" if today's date was either the 1st or 3rd or 5th MONDAY of
the month (else value = false)

Is it possible to do so using a formula?
If not what would be the VBA code required for this?

Would appreciate help with this

TIA
Vinay

 

[Mika]

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

 

[Tom Ogilvy]

Look at Chip Pearson's site and you can combine some of the formulas there
to do what you want:

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

if you want to use VBA, you can find that as well:

http://www.cpearson.com/excel/DateTimeVBA.htm#NDow

 

[vvaidya]

Thanks Mika & Tom
Will give Pearson's site a try
Vinay

 

[K Dales]

Here is a formula in "one shot" that should do it:
=IF(AND(WEEKDAY(TODAY(),2)=1,OR(DAY(TODAY())<8,AND(DAY(TODAY())>14,DAY(TODAY())<22),DAY(TODAY())>28)),"YES","NO")

 

[Ron Rosenfeld]

Hello:

I wanted the value of a cell to change (either "yes" or "No") based on
the following date conditions:

Return "Yes" if today's date was either the 1st or 3rd or 5th MONDAY of
the month (else value = false)

Is it possible to do so using a formula?
If not what would be the VBA code required for this?

Would appreciate help with this

TIA
Vinay

=IF(AND(WEEKDAY(A1)=2,MOD(INT((DAY(A1)-1)/7),2)=0),"Yes","No")

Substitute TODAY() for A1 in the formula; or enter =TODAY() into A1 (or some
other referenced cell).


--ron

 

[vvaidya]

Thanks to K Dales, & Ron.
Both solutions worked
Vinay