More Tips & Tricks!

Here’s another neat Tip or Trick for our fellow FileMaker developers… This one comes from Senior FileMaker developer ~ Rob Woof (Certified FileMaker 11-17 developer)

The “Date” Function

Date ( month ; day ; year )

Constructs a date from the three integer values provided in month, day and year. You must provide 4 digits for year. This is pretty simple and straightforward, but it has some very useful properties when month and day are outside what you would consider their ‘normal range’ (1-12 for months, 1-31 for days).

Out of range month values are interpreted in a logical way. For example, if you try Date ( 13 ; 1 ; 2010 ), the result will be 1 Jan 2011 (in whatever format you set it to display). It’s like the odometer on your car – pushing the month value over 12 makes the year of the result ‘tick over’. Logically, the ’13th month’ of 2010 is interpreted as the 1st month of 2011.

Likewise, numbers below 1 (i.e. zero and negative numbers) decrement the month and/or year by the appropriate number. For example, Date ( 0 ; 2 ; 2010 ) gives 2 Dec 2009. Month 0 of a year is interpreted as the last month of the previous year, and so on backwards.

This behaviour extends (naturally enough) to day values as well. Date ( 12 ; 32 ; 2010 ) would resolve to 1 Jan 2011 – the day after 31 Dec 2010. Likewise, date (12 ; 0 ; 2010 ) would give you 30 Nov 2010. That is, ‘the day before 1 Dec’.

This behaviour is extremely useful in all sorts of situations. For example, if you had a membership system that handled memberships of six months’ duration. When someone joins, the system could set their expiry date to six months in the future using the following calculation:

Expiry = Date ( Month ( Get ( CurrentDate ) ) + 6 ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) )

No matter what the current month is, the above calculation will give the correct expiry date*. For example:

Current Date: 1 October 2010

Expiry = Date ( 10 + 6 ; 1 ; 2010 ) = Date ( 4 ; 1 ; 2011 ) = 1 April 2011

The Date function ‘wraps around’ the month value into the next year. Likewise, to calculate the date some interval in the past, the same principle applies. You can simply subtract the desired number from either the day, month or year of the reference date in the Date function, and FileMaker will simply ‘wrap around’.

On a personal note, when I discovered this behaviour of the Date function, I considered it to be the coolest FileMaker function around. I still think so, several years down the track. It just makes date-handling so simple and trouble-free.

* Sharp-eyed readers will be thinking “What about a six-month period from 31 August? There’s no 31 February!”. Well done! It’s things like that you will need to think about before blindly applying the above calculation.