Check for Leap Year & Change

[Binnit]

2008 being a leap year is causing me a headache on a date calculation - any help welcomed.

I have a start date to which I need to add a year -1 day days and end with a new date exactly one day prior to the original start date e.g.

01/01/08 to 31/12/08
31/08/07 to 30/08/08

I have tried various Dateserial and DateAdd but get cannot get consistent results due to the leapyear.

Assume startdate=31/08/07

Format(DateAdd("yyyy", 1, txtStartDate), "dd/mm/yy")

returns 31/08/08 (I want 30/08/08)

Assume startdate=01/03/07

Format(DateSerial(Year(txtStartDate)+1, Month(txtStartDate),1-1 ), "dd/mm/yy")

Returns 29/02/08 which appears to be correct until you change txtStartdate to 02/03/07 and it still returns 29/02/08.

This is driving me nuts and must be a common problem but I cannot find any previous postings for it.

Happiness is...not getting what you want but wanting what you have already got

 

[alvechurchdata]

Can you just add 365 days instead?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Binnit]

alvechurchdata

I have tried DateAdd 365 days but the results are different in the leap year as follows:-

Dates in UK format dd/mm/yy

for 2006 - 2007 dates (both non leap years)
01/08/06 +365 result = 31/07/07

for 2007 - 2008 dates
01/08/07 +365 result = 01/08/08 I still want 31/07/08

Any other suggestions?

Happiness is...not getting what you want but wanting what you have already got

 

[ck1999]

Am i missing something
shouldn't
Format(DateSerial(Year(txtStartDate)+1, Month(txtStartDate),1-1 ), "dd/mm/yy")

be
Format(DateSerial(Year(txtStartDate)+1, Month(txtStartDate),day(txtstartdate)-1 ), "dd/mm/yy")

ck1999

 

[Binnit]

ck1999

Doh...
No you're not missing something - you are spot on, I am sure that I tried that too! I think it was a case of looking at it too much.

Thanks a bundle - now I can move on with some hair still left!

Happiness is...not getting what you want but wanting what you have already got

 

[SkipVought]

Keep in mind that the FORMAT function returns a STRING, not a DATE.

If you need a REAL DATE (which is just a NUMBER), just use the DateSerial function alone.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[KenReay]

Or to keep your original idea

Format(DateAdd("d",-1,DateAdd("yyyy", 1, txtStartDate)), "dd/mm/yy")

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[danvlas]

DateAdd("yyyy", 1, txtStartDate) - 1
should give you the desired result...

Not tested though...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Binnit]

Hey thanks guys... more solutions for my "dates" library on this one!



Happiness is...not getting what you want but wanting what you have already got