6 Replies Latest reply on Jan 12, 2016 5:02 AM by tkemmere

# Understanding calculation to distract "1 month" from a date

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.

• ###### 1. Re: Understanding calculation to distract "1 month" from a date

Complete description of the calculation is:

This calculation will jump one month back but stay on the matching day. Exceptions: When the prior month's matching DAY does not exist. If DAY doesn't exist, this calc will produce LAST day of the prior month (and accounts for leap year/cross year. Calc conforms to FM's internal dates and responds properly).

• ###### 2. Re: Understanding calculation to distract "1 month" from a date

Your basic tool for this is the Date function. It looks like this:

Date ( month ; day ; year )

So, to subtract one month from a given date, you can use this:

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

FileMaker will automatically account for preceding years, months, etc. So if I put in 1/11/2016, then the calculation will give 12/11/2015.

Now, your requirement is a little different. Why? Because of the requirement that nonexistent dates process out as the last day of the preceding month. The basic calculation would take 2/28/2016 and return 1/28/2016 - that's fine. But if I put in 3/31/2016, it'll return 3/3/2016. Not what you want. So instead, you would do this:

Let ( [

baseDate = Date ( Month ( startDate ) - 1 ; Day ( startDate ) ; Year ( startDate )) ;

prevMonthEnd = Date ( Month ( startDate ) ; 0 ; Year ( startDate ))

] ;

Case ( Month ( baseDate ) < Month ( startDate ) ; baseDate ; prevMonthEnd )

)

HTH

Mike

• ###### 3. Re: Understanding calculation to distract "1 month" from a date

FileMaker's date calculations are able to jump months and years correctly (when using additions & subtractions):

Jan 31 (+ 1 month = Mar ..., depending on leap year/day)

Date ( Month(myDate) + 1 ; Day(myDate) ; Year(myDate) )

Dec 12 (+ 30 days = Jan 11, the next year)

GetAsDate("12/12/2015") + 30

or

myDate + 30

Jan 11 (- 30 days = Dec 12, the previous year)

GetAsDate("1/11/2015") - 30

or

Date ( Month(myDate) ; Day(myDate) - 30 ; Year(myDate) )

and a really COOL feature? mm/0/yyyy = the LAST day of the PREVIOUS month (including back a year if needed)! the "0" for the day value is what says, go back a day from the first day of the listed month/year.

Date ( Month(myDate) ; 0 ; Year(myDate) )

or

GetAsDate("1/0/2016") = 12/31/2015

Min() & Max() works on dates, as well so testing the minimum day for any given day will yield the LAST day if it's less than a calculated date that might bump it to the next month.

HTH,

beverly

• ###### 4. Re: Understanding calculation to distract "1 month" from a date

Dear Mike and Beverly,

Great!

I marked this as the correct answer because both your replies answer my question equally well.

Thanks for putting this in words. I'm going to test it all, so I get the hang of it!

• Mike, what you put in the Let statement, with the Case, makes it more readable. Nice.
• Beverly, I'll definately try that 0-feature as well.

Thanks, regards, Thomas.

• ###### 5. Re: Understanding calculation to distract "1 month" from a date

The is a similar thread on this forum:

A month from today?

HTH,

Beverly

• ###### 6. Re: Understanding calculation to distract "1 month" from a date

I managed! I needed it for contract management.

Took me 2,5 days alltoghether but I now have a script that caculates...

• Phase description (Contract running;1st extension;2nd extension ...10th extension)
• Phase start date
• Phase end date
• Last notice date
• Notice two months before notice

Based on...

• Start date
• Duration (Determined;indertermined)
• Duration in months
• Extension (Determined;indertermined;none)
• Extension duration in months
• Notice (Yes;no)
• Noticeperiod in months

And all to the perfect date! Leapyears, months of 30 and 31, all working. Great!