Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 16, 06:45 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove holidays from calculation Sal Excel Worksheet Functions 9 December 29th 09 03:07 AM
Consecutive date calculation involving holidays BMoran Excel Worksheet Functions 1 November 12th 09 09:57 PM
Date Calculation Formula- Calendar days minus Holidays Amanda Excel Worksheet Functions 1 September 1st 09 09:57 PM
what formula best divides sales of a company with 25 members 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.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017