Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove holidays from calculation | Excel Worksheet Functions | |||
Consecutive date calculation involving holidays | Excel Worksheet Functions | |||
Date Calculation Formula- Calendar days minus Holidays | Excel Worksheet Functions | |||
what formula best divides sales of a company with 25 members | Excel Discussion (Misc queries) |