Forward Dates

[Carly]

I currently enter a date into a text box in a form in the following format:

200401 = Jan2004

From this I would like to work out a future date which will always be the end of February or the end of August depending on which month I am reporting on.

I have done this for August by using the following:

=IIf(Right([Current],2) Between "03" And "08","31/08/" & Left([Current],4), OTHERWISE PUT THE END OF FEBRUARY )

But as February is a moving month instead of hardcoding in the last day of the month, how to I get it to automatically put in the last day of February

I hope this makes sense as it is very hard to explain

Regards
Carly

 

[Mile-O]

Use the first day of March and the DateAdd() function to remove 1 day from it.

 

[ColinEssex]

Or you could establsh if the year is a leap year - if yes then Feb = 29 else its 28.

I'll dig out the code if you need it - although Mile-O's suggestion is easier.

Col

 

[Carly]

I have used the DateAdd function before but I'm not 100% sure how it works.

How would I write the formula to say 1st March minus 1 day?

 

[Mile-O]

=DateAdd("d", -1, DateSerial(Left([Current], 4), 3, 1))

 

[Carly]

Thanks - That's worked a treat

Regards
Carly