Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Dates against Bank Holidays
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Dates against Bank Holidays
=WORKDAY(A1+43,-1,holidays)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary T" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Checking Dates against Bank Holidays
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) "Gary T" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Conditional Formatting to exclude weekends and Bank Holidays | Excel Worksheet Functions | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Conditional formatting to exclude weekend and Bank Holidays | Excel Worksheet Functions | |||
Conditional Formatting & UK Bank Holidays | Excel Discussion (Misc queries) |