How can I calculate workday date when only have calendar days?

Hi All,

From a Pay date, how can I calculate a claim date when I only have calendar days.

Based on the following:

Pay Date = Mon 08/05/2017
Claim date = less 7 calendar days from Pay Date
Bank Holiday = Mon 01/05/2017
Rule 1 = The claim date must be a workday (Mon-Fri) excl bank holidays.
Rule 2 = Only if the initial calculation for the claim date falls on a weekend day or bank holiday, then we need to take the first available date beforehand. Weekend days or bank holidays do not otherwise affect the calculation.

I would expect the claim date to be Fri 28/04/2017.

Due to a combination of only having calendar days AND the 2 rules, I can't see how I can combine the WORKDAY & NETWORKDAYS functions to get the correct results. Does anyone have any idea how to achieve this?

Thanks

Tooley

Try

=WORKDAY.INTL(A1,-5,"0000011",F1)

A1=Pay Day

F1=Holiday (01/05/2017)

For future use, create a named range ("Holidays") and replace F1 with Holidays

WORKDAY.INTL(A1,-5,"0000011",Holidays)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.