Formula needed please

[lapta301]

Hi

Can I please trouble someone for some help with a formula.

With a date in A1 I would like the next date in A2 to show the next 28th of the month providing it is a weekday. If it is a weekend then show the previous Friday.

So with A1 containing 2nd January 2017 A2 would now show Friday 27/1/17.

Just to clarify I would the formula to always show a Friday if the 28th falls on a weekend.

Many thanks for any help that you can provide

 

[BarryL]

maybe:

=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),DAY(28)),2)>5,WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(28)),-1),DATE(YEAR(A1),MONTH(A1),DAY(28)))

 

[Joe4]

Try this:

=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),28),3)>4,DATE(YEAR(A1),MONTH(A1),28)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),28),3)-4),DATE(YEAR(A1),MONTH(A1),28))

 

[lapta301]

Hi Barry & Joe

Many thanks for showing an interest in my problem.

Not sure if I'm doing something wrong or I didn't explain the question properly.

With a date in A1 the formula does produce the correct answer in A2 but if I want to extend the list on it only repeats the value in A2.

So with 2/1/17 in A1, 27/1/17 is the correct answer in A2 but that is repeated onwards when I would like the 28th of February, March etc. or whatever the correct date for the 28th is.

My apologies for not making this clear.

 

[Joe4]

Where are you extending this (what cell addresses)?
How are out are you going?

 

[Tetra201]

Here's a shorter one:

=WORKDAY(EOMONTH(A1,-1)+29,-1)

 

[lapta301]

Tetra

With a slight tweek your formula works fine - changed the first -1 to 0 after putting in the first correct date manually and then I can copy it down as far as I need and get the correct dates. Thank you, much appreciated.

Joe & Barry my sincere thanks for taking an interest. Sorry that I didn't make it clear exactly what I wanted.

 

[Tetra201]

Glad to have helped.