The Last Day of the Month, part 3

Grizzled FileMaker veterans are fond of saying things like, “You ought to know at least three different ways to accomplish any given task.” With that in mind, I hereby submit a third method for calculating the last day of a given month.

Let ( [
theDate = Get ( CurrentDate ) ;
monthNum = Month ( theDate ) ;
yearNum = Year ( theDate ) ;
febLastDay = 28 +
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
) ;
dayNum = Choose ( monthNum ; "" ; 31 ; febLastDay ; 31 ;
30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31
)
] ;

Date ( monthNum ; dayNum ; yearNum )

) // end let

If you read yesterday’s post, you may have noted a resemblance between this calculation and its predecessor, at least as far as the “Let” portion goes. The main difference is the addition of a dayNum variable populated via the Choose() function.

In case you’re not comfortable with Choose(), its format is

Choose (
test ; result if test = 0 { ; result if test = 1 ; result if test = 2... }
)

…where “test” is any non-negative whole number, and the results in braces are optional. At first this function may seem confusing but it turns out to be a very compact replacement for the Case() function, under a strictly defined set of circumstances.

Say, for example, in a table called “test”, you have a field called “score”, which can contain any integer between 0 and 9, and you want convert that value to its corresponding name (“zero,” “one,” “two”, etc.). You could certainly accomplish this with Case() and the statement might look like this:

Case (
test::score = 0 ; "zero" ;
test::score = 1 ; "one" ;
test::score = 2 ; "two" ;
test::score = 3 ; "three" ;
test::score = 4 ; "four" ;
test::score = 5 ; "five" ;
test::score = 6 ; "six" ;
test::score = 7 ; "seven" ;
test::score = 8 ; "eight" ;
test::score = 9 ; "nine"
)

The exact same result can be obtained far more economically thus:

Choose ( test::score ;
"zero" ; "one" ; "two" ; "three" ; "four" ;
"five" ; "six" ; "seven" ; "eight" ; "nine"
)

Essentially, Choose uses test::score as a pointer to the correct “result”, via what’s known as a zero-based index, so a test::score value of 0 corresponds to the first result, a test::score of 1 corresponds to the second result, etc.

In the case of our Last Day of the Month problem, there is no month number of 0, only 1 through 12, so our first result is "" to accommodate the non-existent zero result.

4 thoughts on “The Last Day of the Month, part 3

  1. Dave Messer

    The simplest way to determine the last day of the month for a given date that I’ve come up with:

    lastDayOfMonth = Date(Month(theDate)+1; 1; Year(theDate) ) – 1

    Basically, go to the first of the next month and subtract 1 day.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s