Checking Dates against Bank Holidays

[Gary T]

Hi

I would like to 'formularise' the following logical statements (in column B,
starting from B5):

If column A (starting from A5) + 42

IS EQUAL TO any date in the range Sheet1!A1:M70

THEN

RETURN the most recent date prior to (A5 + 42) that is neither (a) a weekend
date; nor (b) any other date that falls in the range Sheet1!A1:M70

OTHERWISE

A5 + 42


For example, in the range A1:M70, I have all dates that are Bank Holidays
over the coming next few years.

I would like the spreadsheet to allow the formula entered into Cell B5 to
check that, when 42 (i.e. 6 calendar weeks) is added to cell A5 (the Invoice
Date), this date is not a bank holiday date, and if it is, to then return the
date that most recently precedes (A5 + 42), such date being a date that is
not also a Bank Holiday or a weekend day.

For example, if A5 contained 13 November 2007, then A5 + 42 would equal 25
December 2007. However, I would like B5 to return the date that precedes
this Bank Holiday, which would be 24 December 2007, so that the Invoice is
paid on this date, and is not 'set' for the 25th.

Similarly, if A5 contained 14 November 2007, I would like B5 to return 24
December again, as although A5 + 42 is 26 December, 25 December is not
allowable as it is also a Bank Holiday, and therefore the formula has to
refer back a further day, to the 24th.

In a similar way, I would like weekend dates to be unallowable for return in
B5, so if A5 + 42 is a Monday, it return the Friday previous.

Any help greatly appreciated.

 

[Bob Phillips]

=WORKDAY(A1+43,-1,holidays)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Sorry, that should of course be

=WORKDAY(A5+43,-1,holidays)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)