Workdays Formula

[Vivienne]

I need to be able to calculate the date which is 7 working days before a specific date. For example,

Target date is 18 November 2010
7 working days before due date is 9 November 2010
5 working days before due date is 11 November 2010
2 working days before due date is 16 November 2010

What are the formulae I need to get each respective answer?

All clues gratefully received!

[squiggler47]

Assuming that you dont include holidays and that weekends are sat/sun :-

=LARGE(ROW(INDIRECT(B1&":"&B1-B2-3))*(MOD(ROW(INDIRECT(B1&":"&B1-B2-3)),7)>1),B2)

entered with CTRL-SHIFT-ENTER

where B1 is the Ending Date and B2 is the number of working days, it works like an inverse networkdays

[Cheeky Charlie]

or:
workday(<cell with 18th November>,-7)
workday(<cell with 18th November>,-5)
workday(<cell with 18th November>,-2)

For which you need to have the analysis toolpak installed (to check, tools-> addins, check in analysis toolpak)

[daddylonglegs]

You can use WORKDAY function from Analysis ToolPak, i.e. to get 7 working days before a date in A2

=WORKDAY(A2,-7)

If that gives an error then try enabling the ToolPak

Tools > add-ins > tick "Analysis ToolPak"

format result cell as a date

[Vivienne]

Thanks folks, problem solved!