Add Company Holidays to Formula Calculation
I have created the following formula:
=IF(AND(J3<"",L3<""),IF(L3="air",IF(WEEKDAY(J3+[15import.xlsx]Sheet2!$ A$2,2)<6,J3+[15import.xlsx]Sheet2!$A$2,WORKDAY(J3+[15import.xlsx]Sheet2! $A$2,1)),IF(L3="dhl",IF(WEEKDAY(J3+[15import.xlsx]Sheet2!$B$2,2)<6,J3+[1 5import.xlsx]Sheet2!$B$2,WORKDAY(J3+[15import.xlsx]Sheet2!$B$2,1)),IF(L3 ="ups",IF(WEEKDAY(J3+[15import.xlsx]Sheet2!$C$2,2)<6,J3+[15import.xlsx]S heet2!$C$2,WORKDAY(J3+[15import.xlsx]Sheet2!$C$2,1)),IF(L3="ocean",IF(WE EKDAY(J3+[15import.xlsx]Sheet2!$D$2,2)<6,J3+[15import.xlsx]Sheet2!$D$2,W ORKDAY(J3+[15import.xlsx]Sheet2!$D$2,1)))))),"") It calculates the lead time of products ordered (in column K), taking into consideration whether the due date falls on a Saturday/Sunday and moving that date to the next Monday. Unfortunately the formula does not take into consideration the dates when our company is closed for company holidays. How do I modify the formula? Do I have to create some sort of table that includes the company holidays? Thanks. -- tb |
All times are GMT +1. The time now is 09:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com