date calculator

[C.S.]

Hi,

I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:

10 days from -start date- is:
15 days from -start date- is:
20 days from -start date- is:
and so on...

I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.

Thanks,
c.s.

 

[C.S.]

and if that friday before weekend happens to be a holiday as well, it needs to adjust to the day before it (which would be a thurs)

 

[Sandeep Warrier]

Make the following changes in the code...

    Case 7
        NewDate = NewDate [B][COLOR=Red]- 1[/COLOR][/B]
    Case 1
        NewDate = NewDate [B][COLOR=Red]- 2[/COLOR][/B]

and

            If (CLng(Rng.Value) <= CLng(NewDate)) And (CLng(Rng.Value) >= CLng(StartDate)) Then
                NewDate = NewDate [B][COLOR=Red]- 1[/COLOR][/B]
            End If