Date Formula

[jme1013]

trying to write a formula that makes a cell display the 1st Wednesday of the current month.

 

[DonkeyOte]

I suspect there are plenty of better alternatives but the below should return given date for a given year, month, day of week and instance
(ie 2nd Monday of Jan 2034)

=DATE($C$2,$D$2,1)+MOD($A$2-WEEKDAY(DATE($C$2,$D$2,1),2),7)+7*($B$2-1)
where C2 holds Year, D2 Month, A2 day of week (Mon = 1), B2 instance

modify as appropriate
(note pending instance the value returned may reside outside of the given month - eg 10th Tuesday etc...)

 

[AlphaFrog]

I don't know if this is better, but this is what I came up with...

=DATE(YEAR(TODAY()),MONTH(TODAY()),8)
-CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2),5,6,7,1,2,3,4)

This returns the date of the first Wednesday of the month based on today's date.

 

[jme1013]

Thank you both. That was exactaly what I was looking for.

 

[barry houdini]

trying to write a formula that makes a cell display the 1st Wednesday of the current month.

Specifically for that

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+4)

or for a generic version with the setup that Donkeyote suggested try

=DATE(C2,D2,1+7*B2)-WEEKDAY(DATE(C2,D2,7-A2))

 

[jme1013]

Thanks again. Equally as effective.