Add months to a date field.

[Paolo Savignone]

Hi.
I'm new in this group.
I have Sailforms pro.

I need to calculate a date by adding a variable number of months, how do I do it?

Example:
Departure date; Months of increase (or decrease if negative); Date to be obtained with the mathematical equation.
January 31, 2020; 1; February 29, 2020
January 31, 2020; 3; April 30, 2020
January 15, 2021; 3; April 15, 2021
January 5, 2022; 2; March 5, 2022

The mathematical equation "Date () + [numeric field]" gets a new date but adds only the days. Since there are months with 28, 29 (if leap year), 30 or 31 days, I never get the date I need.

Thanks

[JR Gouze]

Hello. To solve this, I add 1 day to the date, then I apply the mathematical equation to add months, and finally I substract 1 day. It works well when the date is the end of a month, whatever the month.

[Doug Packer]

As you already know, adding a 'month' is an ill defined concept due to the variable number of days the month in question represents.  One solution is to add or subtract days instead of months. This puts your input in control of the varied answer.  Failing that, you can build an extensive equation with multiple if commands to automatically add the correct number of days to the original date.

[Paolo Savignone]

Thanks for the quick replies.

I thought it was easier since there are special functions with SQL.
Example in DB2 SQL (IBM AS400 for veterans) is very simple:

SELECT add_months ('31 .01.2020 ', 1)
   FROM SYSIBM.SYSDUMMY1
and returns 29.02.2020.

If you have any other ideas, I'd be happy to read them.
Otherwise I will use a lot of "IF" in the math formula.

Thank you.
Hi

[Doug Packer]

2 small comments, potentially stuff you already know.

1. Large extensive equations are challenging to enter cleanly and even more difficult to edit.  I recommend writing them out on paper before turning to Sailforms entry. You may choose to address this complexity using multiple hidden field calculating intermediate answers.

2.  All commands (functions) are listed under the Commands button.  You may discover help that I failed to consider. However, some of this stuff is just roll you own.

[Mike Wilson]

You could try this. It has lots of intermediate steps to show each stage, but I think it works.

1. Take the input date (Date) e. G.  31-01-2020 and add 3 months

2. Calculate a month number for the new month = 12 x year + months + months to add = 2020*12+1+3=24244

3. Work out the new year from 24244 by deducting 1 and dividing by 12 into a whole number field.

4. Work out the new month by multiplying the result in step 3 by 12 and deducting the answer from step 2

    (steps 2 to 4 take account of going over a year end)

5. Create a string containing 12 characters, one for each month, and each character is a code for the number of

    days in the month N=Normal (31),S=Short (30),F=February (28 or 29).

6. Turn the code in step 5 into a number (separate field as cannot use subString in a number field) with If logic to get 28,29,30 or 31 (using the leap year flag)

6. Work out the new day number by taking the lower of the day in the original date in step 1(31) and the max number of days in the new month..so, resulting date is 30 April (not 1 May)

7. Use toDate() function to convert the new year, month, date fields back to a new date.

See attached

M

[JR Gouze]

So nobody liked my idea of adding 1 day, calculating with + months, then substracting 1 day ?  Looks simpler than other suggestions is it not ?

[MikeG]

Sorry, I had looked at this but did not understand what you meant by "apply the mathematical equation to add months".  However, thinking further I now understand and your proposal is undoubtedly the simplest presented so far.

So, to help others understand, here is the equation you need to add Months to Date:
   toDate(year(Date+1),month(Date+1)+Months,day(Date+1))-1

This certainly seems to work for all the examples I've tried.
   Mike

[Doug Packer]

Hey Mike, test Jan 30 plus one month. I get March 2.

[MikeG]

You are correct Doug, the method fails if the start date is 29th or 30th and the end date should be at the end of February.  29th is OK if the target date is in a leap year!

I claim the method works in all other cases!

I don't think there's a simple solution but maybe the original proposer of the method can tell us otherwise.

[JR Gouze]

Of course my method works ONLY if the date is the LAST day of the month, january 31st ... or february 28 th or 29 th on leap years ... otherwise a complicated method is indeed necessary.

[Mike Wilson]

Hi

Almost there, but would still need to know how many days are in each month.

What about 2 steps:

NewDate = toDate(year(Date+1),month(Date+1)+Months,day(Date+1))-1

Final Date = NewDate - if(abs(day(Date) - day(NewDate)) > 27, day(NewDate), 0)

Where Final Date is the answer. And I think it works for all days in the month. And of course, it could all be put into one cell by repeating the toDate()  equation several times kbut looks horrible).

MW

[Paolo Savignone]

wow
Thanks everyone for the ideas.
I'll try.