Date caluclator to exclude weekends

**singerbatfink** · 08-19-2011, 05:13 AM

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** · 08-19-2011, 05:33 AM

Hello Brian,

See the attached,

Regards

**singerbatfink** · 08-19-2011, 07:05 AM

Thanks Richard, thats perfect.

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

Thanks

Brian

**royUK** · 08-19-2011, 07:57 AM

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

**daddylonglegs** · 08-23-2011, 07:46 AM

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