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).
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 )
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
myDate + 30
Jan 11 (- 30 days = Dec 12, the previous year)
GetAsDate("1/11/2015") - 30
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) )
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.
Dear Mike and Beverly,
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.
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
- 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!