Sort records by month of birth

[Richard]

My database has date of birth records for each of our members and I want to be able to sort them by month of birth so that I can see who’s birthdays are coming up that month (or even that week). When sorting by date of birth it comes up by chronological order only. Is there a way to refine the sorting so that it sorts by month?

Thanks

Richard

[Brendan]

Hi Richard,

Sure, you can do this, but you’ll need to add a Calculation field so that you can extract out the month from the Birth Date field.

You’ll want to use the DATE(Date Field; "pattern") function to get the month number and then sort by that field.

DATE(Birth Date; "MMM") if you want the month to be spelled out (e.g. June). Or DATE(Birth Date; "MM") if you just want the numerical version of the month, which would sort chronologically by month number. Set the Result Type on the Calculation field to Text as the DATE() function returns a Text value.

The date pattern strings you can use can be found here:

http://www.unicode.org/reports/tr35/tr35-31/tr35-dates.html#Date_Format_Patterns

Now set your First Sort Field to be this Calculation field and away you go.

Thanks!

Brendan

[Author]

Posts