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 |
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 )
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")
A note about using TODAY() in your formulas
Smart fields in Beacon recalculate when either:
Any field on the same record updates
A referenced field on another record updates
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 there's a field that includes TODAY() that you want to use, you will want to update something on any records you want to update. You could choose any field, but often it's easiest to have a checkbox for this sole purpose, such as 'Update age?' or 'Recalculate smart fields?'.
Bulk updating this field to be ticked, and unticked again, will ask your smart field to check that it's correct and you'll have accurate data!