WORKDAY function

[Abgar]

Hi guys,

With the below sample data, i have come across a very slight error in the formula contained in I12.

In a nutshell, the contents of G12 is also the Defined Name of a range of public holidays.

I12 gives a Delivery Date, by taking todays date, adding the number of days contained in H12 and taking into account the range of Public Holidays defined by G12.

Where the slight error is, is that if the data is entered ON a Public Holiday, then it doesn't account for TODAY() being a Public Holiday, so it doesn't adjust the delivery date accordingly.

So basically i want to nest and IF formula (i think) so that it gets the value of TODAY() by reading:
If TODAY() is found in the Public Holiday range, then use TODAY()+1, ELSE use TODAY().

If it is also possible to check if the TODAY()+1 date is in the Public Holiday range, then use TODAY()+2.
(This would only be an issue for Christmas Day, as no other date has 2 consecutive Public Holidays).

If anyone can shed some light on this, it would be greatly appreciated.

Cheers

Excel Workbook
	G	H	I
12	NT	4	1/06/2011
...

Excel 2007

Cell Formulas
Range	Formula
G12	=VLOOKUP($E$12,'Raw Data'!$A$1:$QA$5000,($C12+1),FALSE)
H12	=VLOOKUP($E$12,'Raw Data'!$A$1:$QA$5000,($C12+2),FALSE)+$L$12
I12	=WORKDAY(TODAY(),H12,INDIRECT(G12))

 

[GlennUK]

Instead of TODAY() use WORKDAY(TODAY()-1,1,INDIRECT(G12))

So the full formula would be:
=WORKDAY(WORKDAY(TODAY()-1,1,INDIRECT(G12)),H12,INDIRECT(G12))

 

[Abgar]

Perfect Glenn

Thanks so much

 

[GlennUK]

That's great!