Excel 2007 : How do I write a formula that excludes non-working days from the result?

[SamanthaJane]

Hi,

I currently have a a very simple formula in place.

In A1 I enter a date and in A2 the formula is =A1-2

What I would like to know is, is there a way I can adapt this formula so that it will automatically give me the nearest working day that it a minimum of 2 working days from the date in A1?

e.g If A1 is 7th May 2012 (Monday) then my current formula would mean A2 will be 5th May 2012 which is a Saturday. In this scenario, I would like it to automatically change this to Friday 4th May.

I have had a go with the WORKDAY and NETWORKDAY functions but they seem to only calculate the number of working days between 2 dates?

Any advice would be very much appreciated....

[zbor]

Maybe: =A1-2-MAX(WEEKDAY(A1-2,2)-5,0)

[SamanthaJane]

Thanks for the response Zbor. I have just tried the formula you suggested but when I enter it I get the following error message:

"The value you entered is not valid. A user has restricted values that can be entered into this cell."

I have the cell formatted to only accept a date. Do you think this could be causing the problem?

I really appreciate your help...