Beacon

Working with dates in smart fields

Updated a month ago

January date

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

There's two places you might be using dates in smart fields:

  • Manipulating dates in date smart fields - For example, +/- 10 days to another date.
  • Changing the formatting of a date in a short text smart field - For where you want to display a date in a specific format, such as 'Wed, 12th December 1984' or even simply 'December'.

link Manipulating dates in date smart fields

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

The general format of the formulas are:

  • DATEADD( date , quantity , time unit )
  • DATESUBTRACT( date , quantity , time unit )

Some examples:

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

= 30/09/2021

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

= 25/03/2022

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

= 25/09/2023

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

= 22/09/2021

You can still use other formulas in conjunction with / instead of those above too! For example:

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

link Date formatting 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 formula is:

  • DATEFORMAT( date , " format codes " )

Some examples:

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

= 03/09/2021

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

= September

=DATEFORMAT("2021-09-03", "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.

close

Contact Us

It'd be great to hear from you! To reach out, fill in your details below and we'll get back to you.

close

Register for webinar

To join this webinar, please enter your details below and we'll send you details on how to join.



close