Formula to get the weekday date

Hello:

Please refer to attached file.
I need formula in D8 which will give the date close to 14th but need to make sure that it is weekdays.
In the example:
If I select month 1 (Jan) in cell A1, the 14th is Sat, answer i need in cell D8 will be 2/13/2017
If I select month 2 (Feb) in cell A1, the 14th is Tue, answer i need in cell D8 will be 2/14/2017
If I select month 9 (Sep) in cell A1, the 14th is Tue, answer i need in cell D8 will be 9/14/2017

Let me know if you have any questions.
Thanks.

Riz

Please Login or Register  to view this content.

Kind regards
Leo

Hello:

Thanks Glenn and LeoTaxi..

Riz

Try this ...


=DATE(2017,$A$1,15)-LOOKUP(WEEKDAY(DATE(2017,$A$1,15),2),{1;2;7},{0;1;2})

Or:

=WORKDAY(DATE(2017,$A$1,15),-(WEEKDAY(DATE(2017,$A$1,15))<>2))