Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate Payment Due Date to exclude Weekends and Public Holiday
I have a table with
Column A = Invoice Date Column B = Days of Credit Granted Column C = Payment Due Date Column E = List of Public Holiday with the first row as header and subsesquent rows as the actual data what i want to do is to auto generate the payment due date of each invoices so that it won't falls on weekends and public holiday. for example, if [Invoice Date] + [Days of Credit Granted] falls on a saturday, then the due date will be push forward 2 days to monday. i came out with a formula which works pretty fine given that there's no consecutive non-working days of more than 4 days (which would be quite rare). i wonder if there's an easier and cleaner way to do this =IF(A2="","",IF(ISNUMBER(MATCH(A2+B2,$E:$E,0)),IF( ISNUMBER(MATCH (A2+B2+1,$E:$E,0)),IF(WEEKDAY(A2+B2+2,2)<6,A2+B2+2 ,CHOOSE(WEEKDAY (A2+B2+2,2)-5,IF(ISNUMBER(MATCH(A2+B2+4,$E:$E,0)),A2+B2+5,A2+B 2+4),IF (ISNUMBER(MATCH(A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3) )),IF(WEEKDAY (A2+B2+1,2)<6,A2+B2+1,CHOOSE(WEEKDAY(A2+B2+1,2)-5,IF(ISNUMBER(MATCH (A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3),IF(ISNUMBER(MA TCH(A2+B2+2,$E:$E, 0)),A2+B2+3,A2+B2+2)))),IF(WEEKDAY(A2+B2,2)<6,A2+B 2,CHOOSE(WEEKDAY (A2+B2,2)-5,IF(ISNUMBER(MATCH(A2+B2+2,$E:$E,0)),IF(ISNUMBER( MATCH (A2+B2+3,$E:$E,0)),A2+B2+4,A2+B2+3),A2+B2+2),IF(IS NUMBER(MATCH (A2+B2+1,$E:$E,0)),IF(ISNUMBER(MATCH(A2+B2+2,$E:$E , 0)),A2+B2+3,A2+B2+2),A2+B2+1))))) Thanks for ur time : ) Regards, Min Yeh |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Triggering a Flag after 10 days that excludes weekends and holiday | Excel Worksheet Functions | |||
Date count to exclude weekends | Excel Worksheet Functions | |||
Alternative formula to exclude holiday calculation | Excel Worksheet Functions | |||
Include/Exclude Holiday from Automatic Sheet Creation | Excel Discussion (Misc queries) | |||
Date Calculation to exclude weekends | Excel Worksheet Functions |