AnsweredAssumed Answered

Understanding calculation to distract "1 month" from a date

Question asked by tkemmere on Jan 11, 2016
Latest reply on Jan 12, 2016 by tkemmere

Dear all,

 

Is someone available to help me understand the (10 year old) calculation presented on

subtract exactly one month from a date? - Calculation Engine (Define Fields) - FMForums.com

? (I don't have an account there and I prefer to ask here).

 

It says:

1| Let([

2| dayWant=Day(dateField);

3| dayEnd=Day(dateField-dayWant)];

4| Date(Month(dateField)-1;Day(Min(dayEnd;dayWant));Year(dateField)))

 

The purpose is to go back one month in time, in this way:

  • 31st of Jan 2016 > 31 Dec 2015,
  • 31 Dec 2015 > 30 Nov 2015,
  • 31 Mar 2016 > 29 Feb 2016,
  • 31 Mar 2015 > 28 Feb 2015.

 

The calulation works. But I would like to adapt it, so I would need to understand it

 

Say we take 31 Mar 2016 as an example.

 

Line 2: dayWant=Day(dateField);

So what happens: dayWant = the day of "31-Mar-2016" = 31

 

Line 3: dayEnd=Day(dateField-dayWant)];

Sowhat happens: dayEnd = the day of "31-Mar-2016" - 31 = 0

 

Line 4: Date(Month(dateField)-1;Day(Min(dayEnd;dayWant));Year(dateField)))

Sowhat happens: Put date together with:

Month: Mar minus 1 = Feb

Day: lowest of the two options 0 or 31 = 0

Year: 2016

 

Somewhere I'm going wrong. And it must be in line 3, but I can't figure it out. Any help is appreciated.

Please be aware that we're from places where we note dates differently. I thought all this is best readable for everyone using 3 letters for months.

 

Thanks, Regards, Thomas.

Outcomes