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.

Dave MesserThe 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.

Kevin FrankPost authorHi Dave,

Thanks for taking the time to comment.

The approach you mention was covered in part 1.

Regards,

Kevin

DaveThe curse of reading a blog in reverse order, I guess.

Kevin FrankPost authorI appreciate you reading!