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.

Fabrice Nordmann has come up with the simplest leap year test I’ve ever seen:

month ( date ( 2 ; 29 ; year )) = 2

…which returns a 1 if the year is a leap year, or a 0 if it isn’t.

An even simpler method is to construct the date for the first day of the following month, then subtract 1; FileMaker will do the heavy lifting for you:

let (

[ month = 3 ;

day = 1 ;

year = 2399 ] ;

date ( month ; day ; year ) -1

)

gives you

2/28/2399

… while substituting 2400 for 2399 results in

2/29/2400

I agree… how is this different from what was discussed in part 1?

I’m truly embarrassed – 5 mins after posting this I discovered that not only did you discuss this very method in Pt. 1, but even improved on it. I’ll try to read a bit more careful in the future.

Great blog, by the way– thanks for sharing your insights with us!

No problem, we’ve all done it at one time or another.

Thank you for your kind comments.