1 2 Previous Next 16 Replies Latest reply on Oct 27, 2014 9:00 AM by philmodjunk

# Counting years/months instead of days.

### Post

I’ve got a date field “start” and a number field “years”.
The calculation result is a date and the field should be (“start” + “years”) - 1 day

if start is 1/1/2010 and years is 9, then the calculation should be 31/12/2018

I’m trying to make it work for months (other field) and years, but so far it only works if it is in days.
If I start 1/1/2010 + 9 years - 1 day the calculation is 9/01/2010

How do I tell it is a month or year?

Any help welcome.

• ###### 1. Re: Counting years/months instead of days.

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.

Thank you very much!

• ###### 3. Re: Counting years/months instead of days.

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.

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.

I thought it was using the calendar.
Isn't there any workaround to accomplish this?

• ###### 6. Re: Counting years/months instead of days.

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.

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.

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.

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.

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.

You mention "the modified calculation to use" but it looks identical to the previous one.

• ###### 12. Re: Counting years/months instead of days.

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.

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.

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.

1 2 Previous Next