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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate Payment Due Date to exclude Weekends and Public Holiday
Does this do it
=IF(A2="","",WORKDAY(A2,B2,E:E)) HTH Bob "minyeh" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate Payment Due Date to exclude Weekends and Public Holiday
On Mon, 18 Jan 2010 02:54:33 -0800 (PST), minyeh wrote:
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(M ATCH(A2+B2+2,$E:$E, 0)),A2+B2+3,A2+B2+2)))),IF(WEEKDAY(A2+B2,2)<6,A2+ B2,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(I SNUMBER(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 =if(a2="","",WORKDAY(A2+B2-1,1,E2:E100)) I would use a smaller range for holidays than the entire column. If this formula returns the #NAME error, and you are using a version of Excel prior to 2007, look at HELP for the WORKDAY function for instructions to install the Analysis ToolPak. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate Payment Due Date to exclude Weekends and Public Holiday
On Jan 18, 8:49*pm, Ron Rosenfeld wrote:
On Mon, 18 Jan 2010 02:54:33 -0800 (PST), minyeh wrote: 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(M ATCH(A2+B2+2,$E:$E, 0)),A2+B2+3,A2+B2+2)))),IF(WEEKDAY(A2+B2,2)<6,A2+ B2,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(I SNUMBER(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 =if(a2="","",WORKDAY(A2+B2-1,1,E2:E100)) I would use a smaller range for holidays than the entire column. If this formula returns the #NAME error, and you are using a version of Excel prior to 2007, look at HELP for the WORKDAY function for instructions to install the Analysis ToolPak. --ron thanks Bob and Ron for the reply, Ron's reply gives me the result i wanted. It helps a lot : ) Maybe i didn't make myself very clear so Bob gets carried away, i'm not calculating workdays, but the effective last due date of invoices. Just for example, one of my customer buys a ton of coal on 1 Jan 2010 (fri), he was granted 30 days of credit, thus, the due date should be 1 Jan 2010 + 30 days = 31 Jan 2010 (sun), but it falls on a Sunday, when my office is not operating. So, I allow the customer to pay on the next working day, but the next day, 1 Feb 2010 (mon) happens to be a public holiday here in Kuala Lumpur, Malaysia, so the last due date is pushed another days forward to 2 Feb 2010 (tue). Instead of 2 Feb 2010, the first formula gives me 15 Feb 2010. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate Payment Due Date to exclude Weekends and Public Holiday
On Mon, 18 Jan 2010 06:45:16 -0800 (PST), minyeh wrote:
thanks Bob and Ron for the reply, Ron's reply gives me the result i wanted. It helps a lot : ) Glad to help. Thanks for the feedback. --ron |
Reply |
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 |