Remember Me?

#1
January 28th 16, 06:45 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Sal Excel Worksheet Functions 9 December 29th 09 03:07 AM BMoran Excel Worksheet Functions 1 November 12th 09 09:57 PM Amanda Excel Worksheet Functions 1 September 1st 09 09:57 PM payroll check and stub Excel Discussion (Misc queries) 1 February 19th 08 08:20 AM

All times are GMT +1. The time now is 11:03 PM.