Year (date) is one of the family of FileMaker date functions. It does something very simple – it extracts the year out of a given date. Now why would you ever want to do that? I’m glad you asked!

Reasons to use it

Summarising by year

When you want to summarise a set of records by the year of a date, you need access to the year itself. For example, if you wanted to group customer invoices by year (of invoice date), you can certainly sort the records by the date field. However, to summarise you will need a break field which is the year. It is a simple calculation field:

This will be the field used to define the sub-summary part:

calculations based on year

There are a lot of useful calculations that involve dates. It is important to understand that a date is stored as a number. This means that when one date is subtracted from another the result is a number of days. If we are to use this result to calculate a person’s age in years, we would need to divide by 365 and then take the integer of the result. This will never be accurate around a person’s birthday due to having to account for leap years.

The basic calculation of a person’s age takes the current year and subtracts the year they were born. So we use the Year function on each date:

Year ( Get ( CurrentDate ) ) – Year ( DateBirth )

This returns an accurate whole number unless the person has not had their birthday in the current year.

An Accurate age calculation

To perform an accurate age calculation, we must include logic as to whether the person has had their birthday in the current year. In plain English, we would ask when their birthday is this year and compare that to the current date. This requires use of other date functions.

Their birthday in the current year is:

Date ( Month(DateBirth); Day(DateBirth); Year(Get(CurrentDate)) )

So, if this date is greater that the current date, they have not had their birthday – you need to subtract one (1) year from their age result above. Here is the full calculation using the Let function:

Let ([
  DOB = DateBirth;
  today = Get ( CurrentDate );
  birthday = Date(Month(DOB);Day(DOB);Year(today));
  notHadBirthday = (birthday > today)
  ];
  Year (today) - Year (DOB) - nothadBirthday
)

Remember to make this calculation unstored to ensure that it updates correctly each day.

Reasons not to use it

While the Year function is very useful, there are cases when you do not need to create a calculation.

Displaying the year of a date

If you need to display just the year for a particular date, that can most easily be done with date formatting. Select the field (or merge field text object) and set custom date formatting in the Inspector:

A field formatted in this way will display only the year. However, if the user clicks in the field, they will see the full date stored.

Finding records by year

You may be aware of the convention for finding a range of dates. When in Find mode, you enter a two dates separated by an ellipsis (…). For example, this

1/1/2017…31/12/2017

finds all dates in 2017. However, FileMaker allows a shortcut form when searching for all dates in a specific year – simply enter the four digit year and perform the find. FileMaker will construct the above date range for you and perform the search.

This shortcut is also available when searching for all dates in a specific month – searching for 12/2017 will find all dates in December 2017.

The shortcuts can also be used for range searches. Try searching for 2015…2017 and see what is returned.