All Collections
Managing your database
Dynamic fields
Working with dates in smart fields
Working with dates in smart fields

Manipulate dates or change their formatting with date and short text smart fields

Updated over a week ago
January date

There are a lot of ways to manipulate dates via smart fields, and we've even created some of our own functions that aren't even in Excel or Google Sheets!

There's a few ways you might be using dates in smart fields:

Adding or subtracting days/weeks/months

We created some new Beacon functions to easily add or subtract days, weeks, or months from dates - welcome to DATEADD and DATESUBSTRACT!

The general format of the functions are:

  • DATEADD( date , quantity , time unit )

  • DATESUBTRACT( date , quantity , time unit )

Supported time units:

  1. "days"

  2. "weeks"

  3. "months"

  4. "quarters"

  5. "years"

Some examples:

=DATEADD("2021-09-25", 5, "weeks")

= 30/09/2021

=DATEADD({{{Date of birth}}}, 6, "months")

= 25/03/2006

=DATEADD("2021-09-25", 2, "years")

= 25/09/2023

=DATESUBTRACT({{{Due date}}}, 3, "days")

= 22/09/2021

You can still use other functions in conjunction with those above too! For example:

=IF({{{Type}}} = "Volunteer", DATEADD({{{Date 1}}}, 6, "months"), DATEADD({{{Date 1}}}, 3, "months"))

Changing date formats in short text smart fields

For when you need a date to be displayed in a particular way, such as for email templates or document merges, Beacon allows you to display dates in thousands of different formats. That means you can always find a format you'd like!

The general format of the function is:

  • DATEFORMAT( date , " format codes " )

Some examples:

=DATEFORMAT("2021-09-03", "DD/MM/YYYY") 

= 03/09/2021

=DATEFORMAT("2021-09-03", "MMMM") 

= September

=DATEFORMAT({{{date of birth}}}, "ddd, Do MMM YYYY") 

= Fri, 3rd Sep 2021

Here are some useful codes that you can use to mix and match your formats:



Day of month


1 2 ... 30 31


1st 2nd ... 30th 31st


01 02 ... 30 31

Day of week


Su Mo ... Fr Sa


Sun Mon ... Fri Sat


Sunday Monday ... Friday Saturday



1 2 ... 11 12


1st 2nd ... 11th 12th


01 02 ... 11 12


Jan Feb ... Nov Dec


January February ... November December



1 2 3 4


1st 2nd 3rd 4th



70 71 ... 29 30


1970 1971 ... 2029 2030





am pm



0 1 ... 22 23


00 01 ... 22 23


1 2 ... 11 12


01 02 ... 11 12



0 1 ... 58 59


00 01 ... 58 59

Unix timestamp

(Number of seconds since 1st Jan 1970)



You can also include both text and codes to make your desired format. For example, here is the same date with and without extra text:

=DATEFORMAT("2021-09-03", "Do MMMM YYYY")

= 3rd September 2021

=DATEFORMAT("2021-09-03", "On the Do of MMMM, YYYY")

= On the 3rd of September, 2021

We support all codes (or 'tokens') from a library called 'moment.js'. A full list can be found here.

Comparing dates to each other

Why have one date when you can have two? Get the time difference between two dates, or evaluate whether one date falls before another.

Length of time between dates

The general format of the function is:

  • DATEDIF( date 1 , date 2 , time unit )

In plain english you can think of it like this:

  • Difference between( that old date , and this new date, in years/months/days)

Note: Unless you want a negative number, date 1 should be the older date and date 2 should be the more recent.

Supported time units:

  1. "D" - The number of days between them

  2. "M" - The number of complete months between them (ignores remainder)

  3. "Y" - The number of complete years between them (ignores remainder)

Some examples:

=DATEDIF("2021-09-03", "2022-10-01", "Y") 

= 1

=DATEDIF( {{{start date}}}, {{{end date}}}, "D")

= 62

=DATEDIF("2020-01-25", "2022-03-07", "M") 

= 25

You can still use other functions in conjunction with those above too! For example:

=IF( DATEDIF( {{{start date}}}, {{{end date}}}, "D") > 50, "Too long", "On target")

Evaluate whether one date is before another

In Excel or Google Sheets you'd usually compare dates directly using >, <, and =, such as:

=IF( A12 < E5 , "Within range", "Out of scope")

You can do the same in Beacon, but both dates need to be formatted to the ISO standard: YYYY-MM-DD (i.e. not DD/MM/YYYY or a field reference)

e.g. 2023-04-16

=IF( "2023-04-16" < "2023-06-25" , "Within range", "Out of scope")

If referencing fields or non-ISO dates, you could use the DATEFORMAT function to turn them into the ISO format...

=IF( DATEFORMAT({{{date 1}}},"YYYY-MM-DD") < DATEFORMAT({{{date 2}}},"YYYY-MM-DD") , "Within range", "Out of scope")

... however a simpler way to do this is simply to use the DATEDIF function instead, checking if it's a positive number (i.e. after the first date) or negative number (i.e. before the first date):

=IF( DATEDIF( {{{date 1}}}, {{{date 2}}}), "D") > 0, "Within range", "Out of scope")

Get the number of hours between two dates

The simplest way to do this in Beacon is to make use of the DATEFORMAT function, using the "X" output, which outputs the number of seconds since the "Epoch" (1st Jan 1970).

Using this, we can format both dates into the number of seconds, and subtract one from the other to get the number of seconds between them. We can then divide by 3600 to get the number of hours:

=ABS((DATEFORMAT({{{end date}}}, "X") - DATEFORMAT({{{start date}}}, "X"))/3600)

Note: The ABS() function in the above formula makes sure that the number of hours is always a positive number. If you'd like to have a negative number indicate that the 'end date' is before 'start date' (e.g. -24 for a day before), then you can remove the ABS() function.

A note about using TODAY() in your formulas

Smart fields in Beacon recalculate when a field referenced by the smart field is updated. This can be either:

  • A referenced field on the same record

  • A referenced field on a linked record

Today's date isn't a field that's updating, so whilst your formula will calculate correctly when you create it, as soon as tomorrow comes it might not be accurate anymore!

For example:

You have a field that calculates 'Age' using the years between someone's date of birth and TODAY(). Bob is 65, but his birthday is tomorrow. When his birthday comes and goes, his 'Age' will still show 65, unless something triggers the smart field to recalculate and update to 66.

Workaround: Manually trigger the field to update.

If you want to filter or report on a smart field that includes TODAY(), you will first want to make sure that your smart field is up to date. You can do this by updating any field that the smart field references and you'll need to do this for all records you want updated. Often it's easiest to have a checkbox field for this sole purpose, such as 'Recalculate age?' or 'Recalculate smart fields?'.

Include this new field in your formula, so that changing your new field will trigger the smart field to recalculate.

For example, you can calculate someone's age using this formula. In Beacon, your base 'Age' formula might be:

=DATEDIF({{{date_of_birth}}},TODAY(), "Y")

However, the Date of birth field is likely never going to be updated so if you add a checkbox called 'Recalculate age?', and add it to your formula it should look like this:

=DATEDIF({{{date_of_birth}}},TODAY(), "Y") & LEFT("{{{c_recalculate_age}}}",0)

Bulk updating this checkbox field to be ticked, and unticked again, will ask your smart field to check that it's correct and you'll have accurate data!

Did this answer your question?