Hi everyone... got a simple yet no so simple question... what would be the script to add a year minus one day to a date? Like in insurance policies which expire one year less a day from the day you bought it? Seems complex because some months have 31 days.. some 30, some 29 on leap years n 28 on other....

Thanks!

CAN YOU JUST ADD 364 DAYS...?? Please show your script so we can help you..

Actually, adding 364 days is not the answer due to leap years. Try adding:

Date ( Month ( StartDate ) ) ; Day ( StartDate ) ; Year ( StartDate ) ) -1

where: StartDate is the start of the policy.

Small change - you forgot to add one year. :^)

Date ( Month ( StartDate ) ; Day ( StartDate ) ; Year ( StartDate ) + 1 ) -1

OK, revision, this seems to work, let me know..

Case(

Day ( startdate-1 ) >Day ( startdate ) ; Date ( Month ( startdate )-1 ; Day ( startdate-1 ) ; Year ( startdate ) +1);

Day ( startdate-1 ) < Day ( startdate );Date ( Month ( startdate ) ; Day ( startdate-1 ) ; Year ( startdate ) +1))

You missed adding a year and you have an extra ) after month

Date ( Month ( AnyDate ) ; Day ( AnyDate ) ; Year ( AnyDate ) + 1) -1

update, must be a slow day around the offices. LOL :giggle2:

Automatic message

This topic has been moved from "Database Schema & Business Logic → Managing Scripts" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".