Dateserial help with future dates excluding a date range

[qster]

Hello,

I created a form to calculate future dates based on an initial date entered into a form box.

Where I am stumbling is setting an IF statement where I want to calculate 1 year future dated, but exclude a certain date range.

i.e. Start date = July 1, 2006 and I want to future date for a year, but exclude the dates in between Dec. 1 to Mar. 31. Is this possible? or do I need to create another expression or IF.

 

[rjoubert]

So you want to add a year (365 days) PLUS the number of days between the specified date range (89 days in this case)?

 

[rjoubert]

If that's the case, try this...

DateAdd("d", 365 + DateDiff("d", #12/1/2006#, #3/31/2007#), #7/1/2006#)

 

[qster]

Will these dates apply to on-going years or would I need to restate the dates?

Thank you

 

[rjoubert]

Are you ALWAYS going to December 1st (of the previous year) to March 31st (of the current year) as the date range you want to leave out of your calculation? If so, you can build your dates...

Dim dtmStart as Date
Dim dtmEnd as Date

dtmStart = CDate("12/1/" & Year(Date())-1)
dtmEnd = CDate("3/31/" & Year(Date()))

Keep in mind if you run this in December 2006, and you're expecting to see 12/1/2006 and 3/31/2007, it won't work. It will always show 12/1 of the previous year and 3/31 of the current year.

 

[qster]

I need a formula that will exclude all dates from Dec. 1 to Mar. 31 regardless of any year for the date calcuation.

So if I have a StartDate of Jan. 14 200X, the future date of say 90 days forward should be June 29, 200x (30 in April + 31 in May + 29 in June = 90 days)

The Datediff only works for 1 year and not all future years for some strange reason.

 

[qster]

So you want to add a year (365 days) PLUS the number of days between the specified date range (89 days in this case)?

I wanted to add 270 days (future dated) from the time of the StartDate, but to not count the days in between Dec. 1 to Mar. 31 of any year.

So if the Start date is July 1, then the answer should be July 26 of the following year. It works with this equation.
However if I use a Jan. 1 StartDate, it gives me Jan. 26 of the following year. The correct future date should be April 26