Working date calculator

[tniknaks]

Good Afternoon All,

I've been pulling my hair out over a calculator I've been asked to create.

Various formulas needed for different dates have my head in a spin.

The result in H16 must be 3 calendar Months previous to the start date in H3
The result in H20 must be 15 working days in advance of H3.
The result in H23 must be 10 working days previous of H3.
The result in H26 must be 15 working days in advance of H3.
The result in H29 must be 10 working days previous of H20.



All calculated dates must be working days. Any date which lands on a Saturday, Sunday or bank holiday, must show the PREVIOUS working day as the result.

I have a list of weekends and bank holidays stored on sheet2 a1:a564.

Any help would be most appreciated.

Noel.

Edit: I'm using Microsoft Excel 2007.

[DonkeyOte]

re: calendard months - see EDATE in XL Help
re: Working Days calculations see WORKDAY function in XL Help

In versions prior to XL2007 you would have to activate the Analysis ToolPak for both of the above.

[tniknaks]

Analysis ToolPak is enabled. I've looked at NETWORKDAYS and WORKDAYS etc, but being an Excel virgin, it all goes straight over my head.

[daddylonglegs]

Assume that your holiday range is named holidays.....

You can get 3 months before H3 with this formula

=EDATE(H3,-3)

but that might land on a non-working day so you can get that date or corrected (previous workday) by using

=WORKDAY(EDATE(H3,-3)+1,-1,holidays)

for H20 you can simply use

=WORKDAY(H3,15,holidays)

and similar for other calculations

[DonkeyOte]

Examples:

Please Login or Register  to view this content.

Re: H20 etc... WORKDAY rather than NETWORKDAYS

Please Login or Register  to view this content.

NOTES:
1. your Sheet2 table needs to only contain the Public Holiday Dates - weekends are excluded automatically.
2. the ATP functions are available by default in XL2007+ as such you don't need to activate the Add-In