Yesterday we explored a method to calculate the last day of the month, and by way of introduction I said,
You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years.
This time around let’s look at that method, and those alleged “gymnastics”. They turn out to not be terribly convoluted; in fact, the most time-consuming part of this could be simply defining the rules for what constitutes a leap year.
But first let’s pretend there’s no such thing as a leap year or a leap day. In that case we could write our calculation as follows:
Let ( [
theDate = Get ( CurrentDate ) ;
monthName = MonthName ( theDate ) ;
yearNum = Year ( theDate )
] ;
Case (
monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
monthName = "February" ; Date ( 2 ; 28 ; yearNum ) ;
<etc.>
Okay, that was a pleasant interlude, but now back to reality. Superficially, it appears that all years evenly divisible by 4 are leap years, but it’s a bit more complicated than that. A year is a leap year when:
- it is evenly divisible by 400
or
- it is evenly divisible by 4 but not evenly divisible by 100
It’s important to know all the rules, because we don’t want our database breaking on February 29th in the year 2400, do we?
So how do we determine “even divisibility”? We do so by using the Mod() function, which returns the remainder when you divide one number by another. If the result of the Mod() operation is zero, then we know the first number is evenly divisible by the second.
For example, Mod ( 2000 ; 400 ) = 0, which passes test #1 above, and tells us that the year 2000 was a leap year. The year 1900 cannot pass either of the above tests, which tells us it was not a leap year. Bearing in mind that FileMaker uses calculation “short-circuiting” (stops calculating as soon as one of its logical tests evaluate as true), here’s a calc that returns a 1 for leap years and otherwise returns a zero:
Let ( yearNum = Year ( Get ( CurrentDate ) ) ;
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
)
) // end let
Okay, now that we’ve gotten to the heart of the matter, we can put it all together and write our calculation.
Let ( [
theDate = Get ( CurrentDate ) ;
monthName = MonthName ( theDate ) ;
yearNum = Year ( theDate ) ;
possibleLeapDay =
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
)
] ;
Case (
monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
monthName = "February" ; Date ( 2 ; 28 + possibleLeapDay ; yearNum ) ;
monthName = "March" ; Date ( 3 ; 31 ; yearNum ) ;
monthName = "April" ; Date ( 4 ; 30 ; yearNum ) ;
monthName = "May" ; Date ( 5 ; 31 ; yearNum ) ;
monthName = "June" ; Date ( 6 ; 30 ; yearNum ) ;
monthName = "July" ; Date ( 7 ; 31 ; yearNum ) ;
monthName = "August" ; Date ( 8 ; 31 ; yearNum ) ;
monthName = "September" ; Date ( 9 ; 30 ; yearNum ) ;
monthName = "October" ; Date ( 10 ; 31 ; yearNum ) ;
monthName = "November" ; Date ( 11 ; 30 ; yearNum ) ;
monthName = "December" ; Date ( 12 ; 31 ; yearNum )
)
) // end let
One of the nice things about yesterday’s elegant little calculation was that we didn’t have to think about leap year at all. We let the FileMaker calculation engine worry about that for us. But there’s something very different I like about today’s calculation… it’s supremely readable. It’s not elegant, and it’s not succinct, but it gets the job done. And that’s one of my favorite things about FileMaker: there is rarely only one way to solve a problem.
