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
Such as +/- 10 days from another dateChanging date formats
For where you want to display a date in a specific format, such as 'Wed, 12th December 1984', or even simply 'December', in a short text field.Comparing dates to each other
Calculating the time difference between two dates, or whether one date is before another
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:
"days"
"weeks"
"months"
"quarters"
"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:
| Code | Output |
Day of month | D | 1 2 ... 30 31 |
| Do | 1st 2nd ... 30th 31st |
| DD | 01 02 ... 30 31 |
Day of week | dd | Su Mo ... Fr Sa |
| ddd | Sun Mon ... Fri Sat |
| dddd | Sunday Monday ... Friday Saturday |
Month | M | 1 2 ... 11 12 |
| Mo | 1st 2nd ... 11th 12th |
| MM | 01 02 ... 11 12 |
| MMM | Jan Feb ... Nov Dec |
| MMMM | January February ... November December |
Quarter | Q | 1 2 3 4 |
| Qo | 1st 2nd 3rd 4th |
Year | YY | 70 71 ... 29 30 |
| YYYY | 1970 1971 ... 2029 2030 |
AM/PM | A | AM PM |
| a | am pm |
Hour | H | 0 1 ... 22 23 |
| HH | 00 01 ... 22 23 |
| h | 1 2 ... 11 12 |
| hh | 01 02 ... 11 12 |
Minute | m | 0 1 ... 58 59 |
| mm | 00 01 ... 58 59 |
Unix timestamp (Number of seconds since 1st Jan 1970) | X | 1681469292 |
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:
"D" - The number of days between them
"M" - The number of complete months between them (ignores remainder)
"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!