Date function workdays only

[Benjaminmin]

Hi.

I would like an excel formula in say B1 to minus a month from the date A1, but only return a workday. If the date returned is not a workday, I would like it to return the next working day (positive only).

Thank you for your help.

 

[RichardMGreen]

Try this:
=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))=7,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))+2,IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)))=1,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))+1,DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))))

It's not the neatest of formulas but it seems to work.

 

[Benjaminmin]

That is great.

Thank you very much!

 

[JugglerJAF]

A shorter formula using EDATE (and there may still be a better way to do this) is:
=IF(WEEKDAY(EDATE(A1,-1),2)=6,EDATE(A1,-1)+2,IF(WEEKDAY(EDATE(A1,-1),2)=7,EDATE(A1,-1)+1,EDATE(A1,-1)))

Basically it's saying that if the weekday of the data in A1 minus 1 month is 6 (Saturday) then subtract 1 month and add two days, if it's 7 (Sunday), then subtract 1 month and add one day, otherwise (Monday to Friday), just deduct the single month.