Delivery Dates with weekends and public holidays

[charlie25]

Hi All,

I'm trying to build a tool to lookup delivery times in Excel 2007 and I'm a bit stuck. The requirements i have are;

- Each suburb can have a different delivery timeframe, these are located on a separate sheet
- Delivery times are in working days so it needs to account for weekends and public holidays (public holidays vary by state and are also on a separate sheet)
- Don't want to use VBA because macros are disabled by default

What i have so far is;

Excel Workbook
	E	F	G	H	J
5	Delivery Postcode	Suburb	State	Expected Business Days for Delivery	Earliest possible delivery date
6	2000	DAWES POINT	NSW	1	17/02/2011
7		HAYMARKET	NSW	3	21/02/2011
Three

Excel 2007

Cell Formulas
Range	Formula
F6	=VLOOKUP($E$6,'Raw Data'!$A$1:$QA$5000,$D6,FALSE)
F7	=VLOOKUP($E$6,'Raw Data'!$A$1:$QA$5000,$D7,FALSE)
G6	=VLOOKUP($E$6,'Raw Data'!$A$1:$QA$5000,($D6+1),FALSE)
G7	=VLOOKUP($E$6,'Raw Data'!$A$1:$QA$5000,($D7+1),FALSE)
H6	=VLOOKUP($E$6,'Raw Data'!$A$1:$QA$5000,($D6+2),FALSE)
J6	=WORKDAY(TODAY(),H6,(VLOOKUP(G6,'Public Holidays'!A:C,3,FALSE)))
J7	=WORKDAY(TODAY(),H7,(VLOOKUP(G7,'Public Holidays'!A:C,3,FALSE)))

The problem is with the formula in J6 and J7, it's meant to look for any holidays in NSW from the public holidays sheet and add a day for each one to the delivery time. 2 problems;

1. Because i've used vlookup it will only pick up the first row of public holidays, any suggestions how i can make it look for all holiday dates in NSW (or another state the delivery is going to). I can rearrange the holiday dates pretty much however i need to.

2. If there's a public holiday today, it doesnt add an extra day to the delivery period. It works fine if the PH is any day after today.

I'm still learning with Excel so if anyone can suggest solutions to these probs and/or how to simplify what i have that would be awesome.

Cheers,

Charlie

 

[Misca]

Have the public holidays in named ranges and use the INDIRECT function to choose which holiday range it should use in the calculations. Also, you're going to need to add the Public Holidays -names to a column in your lookup list (or have them in a separate lookup table) so that the lookup function returns the name of the Public Holiday range for each postcode. So the basic formulas would be something like:

=WORKDAY(TODAY(),H7,INDIRECT( any function that returns the name of the range you need to use for the postcode ))

If your named range for NSW holidays would be NSW then you could simply use:

=WORKDAY(TODAY(),H7,INDIRECT(G7))

 

[charlie25]

Thanks Misca,

I'm using INDIRECT now and it works great to fix problem 1, but I'm still having problem 2.

If it's 16/2 and i enter 16/2 as a public holday in my NSW holidays range it doesn't add a day to the delivery time. If I add a holiday on 17/2 or later it works fine.

Anyone have any ideas how to fix this? I guess the problem is the workday function is counting forward from 16/2 so isn't taking a holiday on 16/2 into account? I tried the below so it would start counting from 15/2 but there's a formula error in the today function.

=WORKDAY(TODAY(-1),H6,INDIRECT(G6))+1

Cheers,

Charlie

 

[Misca]

Seems to work for me:

Excel Workbook
	F	G	H	I
5	Pick Up Date	State	Duration (days)	Delivery date
6	15.2.11	NSW	1	17.2.11
7	15.2.11	QLD	1	16.2.11
8	15.2.11	SA	3	21.2.11
9
10		Public Holidays
11		NSW	QLD	SA
12		16.2.11	18.2.11	16.2.11
13				18.2.11
Sheet

I used 3 named ranges in my example ("NSW"=G12, "QLD"=H12,"SA"=I12:I13). Just make sure your Holiday Dates are real dates.

Also, if you add +1 to the date the workday function returns, you're going to get Saturday if the function returns Friday. Might be better is you added the +1 to the Days Required -part of the formula:

=WORKDAY(TODAY(-1),H6+1,INDIRECT(G6))