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:

A note about using TODAY() in your formulas


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:

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:

  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")


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!

Did this answer your question?