
1. Re: Counting years/months instead of days.
philmodjunk Sep 24, 2014 1:35 PM (in response to StefanSaeys)A date stores its value as the number of days from 12/31/0000 to the date shown in the field.
Let ( [ m = Month ( start ) ;
y = Year ( start ) ;
d = day ( start ) ] ;
Date ( m ; d ; y + years )  1
) // let 
2. Re: Counting years/months instead of days.
StefanSaeys Sep 24, 2014 2:21 PM (in response to StefanSaeys)Thank you very much!

3. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 2:06 PM (in response to StefanSaeys)I'm also trying to get "start" minus 18 months + 1 day. In most cases the outcome is correct, but sometimes it's off by a 1 or 2 days.
Strange but mostly "start" dates around the 1st of the month seem to be faulty.31/03/2016  18 months + 1 day should be 1/10/2014 but is 2/10/2014
Let ( [ m = Month ( start) ;
y = Year ( start ) ;
d = Day ( start ) ] ;
Date ( m  18; d ; y ) + 1
) // let 
4. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 2:26 PM (in response to StefanSaeys)That's actually the correct result for the calculation specified.
It's because d = 31 and not all months are 31 days in length.
Subtracting 18 months produces a "date" of
September 31, 2014. That will then "adjust" to October 1, 2014. Then the calc adds 1 to produce the Oct 2, date.

5. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 2:33 PM (in response to StefanSaeys)I thought it was using the calendar.
Isn't there any workaround to accomplish this? 
6. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 2:39 PM (in response to StefanSaeys)What are you trying to accomplish? What's the significance of adding 1 to the date? Are you trying to specify a date that always falls on the first of the month?

7. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 2:54 PM (in response to StefanSaeys)The start date defines the beginning of a lease period. It can be any date.
The earliest renewal date for the lease contract (by law) is minus 18 months + 1 day before expiration of the lease. 
8. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 3:08 PM (in response to StefanSaeys)so subtracting 18 months must produce a valid date in the computed month (ie by calendar), then you can add 1 to the result.
There may be a simpler calculation, but this seems to work when I plug in your 31/03/2016 date:
Let ( [ m = Month ( start) ;
y = Year ( start ) ;
d = Day ( start ) ;
d1 = Min ( d ; 28 ) ;
dc = Date ( m  18; d1 ; y ) ;
dd = day ( Date ( Month ( dc) + 1 ; 0 ; year ( dc) ) ) ] ;
if ( dd < d ; Date ( Month ( dc ) ; dd ; year ( dc ) ) ; dc ) + 1
) // let 
9. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 3:29 PM (in response to StefanSaeys)It's getting complicated :)
It works for 31/03/2016 but unfortunately not for the following examples:
30/11/2009 should be 1/06/2008 instead of 29/05/2008
30/09/2011 should be 1/04/2010 instead of 29/03/2010
30/07/2000 should be 30/01/1999 instead of 29/01/1999 
10. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 3:57 PM (in response to StefanSaeys)by my calculations, 30/11/2009 should produce 31/05/2009 as there are 31 days in May so adding 1 day makes it the 31st of May.
For the same reason, 30/09/2011 >> 3/31/2010 and 30/07/2000 >> 1/31/1999
Or did I miss another detail here?
If I am right, then this is the modified calculation to use:
Let ( [ m = Month ( start) ;
y = Year ( start ) ;
d = Day ( start ) ;
d1 = Min ( d ; 28 ) ;
dc = Date ( m  18; d1 ; y ) ;
dd = day ( Date ( Month ( dc) + 1 ; 0 ; year ( dc) ) ) ] ;
if ( dd < d ; Date ( Month ( dc ) ; dd ; year ( dc ) ) ; dc ) + 1
) // let 
11. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 4:10 PM (in response to StefanSaeys)when using your calculation 30/11/2009 produces 29/05/2008 instead of 1/06/2008
You mention "the modified calculation to use" but it looks identical to the previous one.

12. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 4:21 PM (in response to StefanSaeys)By my figures the correct value is 30/05/2008 not 1/06/2008 as there are 31 days in May.
Don't know how I got the previous version pasted but this is the modified version:
Let ( [ m = Month ( start) ;
y = Year ( start ) ;
d = Day ( start ) ;
d1 = Min ( d ; 28 ) ;
dc = Date ( m  18; d1 ; y ) ;
dd = Day ( Date ( Month ( dc) + 1 ; 0 ; Year ( dc) ) ) ] ;
If ( dd < d ; Date ( Month ( dc ) ; dd ; Year ( dc ) ) ; dc + d  d1 ) + 1
) // let 
13. Re: Counting years/months instead of days.
StefanSaeys Sep 25, 2014 4:38 PM (in response to StefanSaeys)I am completely lost now ;)
with the new calculation 30/11/2009 produces 31/05/2008 instead of 1/06/2008 
14. Re: Counting years/months instead of days.
philmodjunk Sep 25, 2014 4:45 PM (in response to StefanSaeys)Which is the correct result. There are 31 days in May. Subtracting 18 months from Nov 30, 2009 gives you a date of May 30, 2008. Add 1 and you get May 31, not June 1.