Date caluclator to exclude weekends

[singerbatfink]

I have a spreadsheet that calculates a future date based on a fixed start date, by inserting the number of weeks you would like it to project.

I.e

Start Date: 01/01/2011 (Saturday)
No Weeks: 14
End Date: 09/04/2011 (Saturday)

I use this to arrange appraisal interviews.

Now if someone starts work for us on either a Saturday or Sunday, the calculation will naturally show a Saturday or Sunday date, however many weeks into the future.

We don’t interview at the weekends so Id like the calculator to show the first Monday date following a Saturday or Sunday. In this example it would be 11/04/2011. Also if the formula could display the Day of the week that would be better, i.e Monday 11/04/2011

Can any one help.

Sample sheet attached.

Brian

[Richard Buttrey]

Hello Brian,

See the attached,

Regards

[singerbatfink]

Thanks Richard, thats perfect.

As an afterthought would it be possible to build in a list if bank holiday dates.?

Thanks

Brian

[royUK]

NETWORKDAYS Function allows for a list of Bank Holidays. Check the Help files

[daddylonglegs]

This formula in C3 will give the same result as Richard's

=WORKDAY(A3+B3*7-1,1)

If you have a list of holidays, e.g. in H2:H10 then you can add that too

=WORKDAY(A3+B3*7-1,1,H$2:H$10)

Note that WORKDAY is part of Analysis ToolPak add-in if you are using Excel versions earlier than 2007

[singerbatfink]

Thanks Richard, RoyUK and daddylonglegs.

This works perfectly now..

Cheers

Brian