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
|