Weekdays function

[Oldham_mark]

Hi

I am trying to find a formula that will bring up the previous working day if the day I input is not a working day (due to either a weekend or Bank Holiday). If the day input is a working day I would like to bring back that day.

If I input a date in cell A1, and wish to return the corrected date in cell B1, what formula would I need in B1?

I have been using =WEEKDAY(A1+1,-1) but this only corrects the date for weekends, not Bank Holidays

Many Thanks

 

[AlphaFrog]

Try the WORKDAY function. It allows for a custom list of holidays.

 

[steve the fish]

Weekday has a provision for holidays as an optional 3rd argument. Put your bank holidays into a list and either create a named range and use that in your formula or just use the cell references with the bank holiday dates in them.

 

[steve the fish]

Sorry meant workday! This for example:

=IF(WORKDAY(A1-1,1,holidays)=A1,A1,WORKDAY(A1,-1,holidays))

 

[barry houdini]

=IF(WORKDAY(A1-1,1,holidays)=A1,A1,WORKDAY(A1,-1,holidays))

It should be sufficient to use this version

=WORKDAY(A1+1,-1,holidays)

 

[Oldham_mark]

It should be sufficient to use this version

=WORKDAY(A1+1,-1,holidays)

This works perfectly. Thanks all