provision for holidays with EDATE

[Benjaminmin]

Hi,

I have a long list of dates and corresponding prices, which I need to do a 9-monthly comparison on (i.e. i take date A, and then the same date but 9 months later, which would just be edate(A,9)).

However, the problem is that the date list excludes weekends and holidays, but edate doesnt account for that obviously, so I need something that will take the date 9 days from now, but if its a sat/sun/holiday (i.e. a date not existing in the list) I just want the next possible days value.

I have -almost- done it with: =INDEX(A$4:A$10000,MATCH(VLOOKUP(EDATE(C6,9),A4:A10000,1),A$4:A$10000,0)+1)

for example if the start date (C6) is friday 18/07/1997 and you just use edate,9 you would get the value 18/04/1998 (which is a saturday).

The above formula, correctly returns 20/04/1998, which is the following monday (eureka!).

My problem now though is that it will always add a day, so even if it landed on a Tuesday, it will return Wednesday's date.

So what I need from you guys, please, is some sort of IF constraint to the formula, so it ONLY adds one day IF the proposed EDATE,9 returns a value that doesnt exist in the line.

If anyone could help me it would be a miracle.

Thanks in advance

 

[Benjaminmin]

I guess this is perhaps not possible to do then?

 

[Benjaminmin]

Never mind, you can lock this.
I figured out a way to do it myself:
=IF(ISNA(VLOOKUP(EDATE(C6,9),A4:A10000,1,0)),INDEX(A$4:A$10000,MATCH(VLOOKUP(EDATE(C6,9),A4:A10000,1),A$4:A$10000,0)+1),EDATE(C6,9))