Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 84
Default Want to Improve Formula

I managed to create 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)))))),"")

Which basically calculates delivery dates for products that I purchase.
If a delivery date falls on a weekend, the formula automatically
adjusts the delivery date to the next available Monday.

The formula does its basic job but I would like to improve things.
While the formula can detect if a delivery date falls on a weekend, it
cannot detect if it falls on any other type of company holiday which
falls on a weekday. (For instance, Christmas this year falls on a
Friday.)

Common sense tells me that I need to create some sort of Excel table
that includes all the company holidays that fall on a weekday, but how
would I integrate that to the existing formula?

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
Trying to improve efficiency of array formula Anthony[_4_] Excel Worksheet Functions 8 November 25th 08 11:23 AM
improve formula, Possible Genious needed !!! Corey Excel Worksheet Functions 8 November 9th 06 11:57 PM
improve formula offset and indirect John Contact Excel Worksheet Functions 1 June 17th 05 07:28 AM
How can improve this formula? Metallo Excel Worksheet Functions 7 January 27th 05 06:49 PM
Need to improve a formula Brian Excel Worksheet Functions 2 December 9th 04 08:17 PM


All times are GMT +1. The time now is 10:01 AM.

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

About Us

"It's about Microsoft Excel"