Examples of Date & Time Functions
Smart Flows comes with a large number of date & time functions - this page shows a few examples to give you an idea of how to use them.
Add days to date
If you want to add 5 days to the current date:
addDaysToDate(currentDate(),5)If you want to add 5 days to a certain field that contains a datetime:
addDays(myfield,5)Where myfield is a field inserted via the Insert Field button in the expression editor.
Format a date in a certain way
If you want to format the current date like July 7, 2015 / August 23, 2012 / November 11, 2016 etc:
formatDate(currentDate(),'MMMM d, yyy')
More information on how to define patters can be found on Date & time patterns.
Get the number of days between two dates
If you want to get the number of days between two fields that both contain datetimes:
floor(toDays(periodBetween(fieldA,fieldB)))Where fieldA and fieldB are fields inserted via the Insert Field button in the expression editor.
Explanation:
- periodBetween returns a period spanning the time between the two provided fields.
- toDays converts that period into a decimal number representing the number of days.
- floor rounds that number down so that you always end up with whole days.
Convert a text string, representing a date in a certain locale, into a usable date
If you want to convert the text string 8 juin 2009 (this is French) that is stored in the myfield field into a usable date such as 08-06-2009:
toDateFR(myfield,'d MMMM yyyy','fr')Where myfield is a field inserted via the Insert Field button in the expression editor.
Explanation:
- d MMMM yyyy is the pattern that follows from 8 juin 2009. It's important that you match your pattern to the content of the field. If you field contains 2009/juin/08, use yyyy/MMMM/dd as the pattern. More info on Date & time patterns.
- fr is the locale for general French.
Find the current day
If you want to see today's number (as in: now it's September 25th, so 25):
formatDate(currentDate(),'d')Explanation: this works because d is the pattern for the day (see Date & time patterns) - so you're basically asking Smart Flows to format the current date into just one number: that of the day.
Find the next month
If you want to see the number of the next month (for instance: now it's October (10), so it would output 11):
formatDate(addMonthsToDate(currentDate(),1),'M')Explanation:
- addMonthsToDate(currentDate(),1) will return the date equal to today + 1 month. If it's October 5th, it will return November 5th.
- formatDate( ... , 'M') will return the month (see Date & time patterns). So if we ask for the month of today + 1 month, it will return next month.
Display the first day of the next month
If you want to display a line that shows the date of the first day of the next month, like Thursday 1 December, 2016 when it's currently November 2016:
formatDate(firstDateOfMonth(addMonthsToDate(currentDate(),1)),'EEEE d MMMM, yyyy')Explanation:
- addMonthsToDate(currentDate(),1) will return the date equal to today + 1 month. If it's November 25th, it will return December 25th.
- firstDateOfMonth(...) will return the date as if it was the first day of the current month.
- formatDate(...) will return the date in the correct formatting. More info on Date & time patterns.
Compare dates
If you want to check whether a date (fieldA) comes before another date (fieldB):
lt(fieldA,fieldB)Where fieldA and fieldB are fields inserted via the Insert Field button in the expression editor.
Explanation: lt(a,b) returns true when a is lower than b, otherwise it returns false. This also works for dates.
'lt' stands for 'lower than' and this is just one of many functions you can use to compare values:
- eq - check whether 2 values are equal
- neq - check whether 2 values are different
- gt - check whether the first value is greater than the second value
- geq - check whether the first value is greater than or equal to the second value
- lt - check whether the first value is lower than the second value
- lteq - check whether the first value is lower than or equal to the second value
Create a period of a number of days
If you want to create a period of 60 days:
createPeriodDHM(60,0,0)This is pretty useful to set up in a pseudo-field, after which you can then refer to the value of that pseudo-field in other expressions. Should the length of the period ever change, you can then just change it once in the pseudo-field instead of having to change it in every expression.
Break down a period into months and days
If you want to present the recipient of your document with a text string that reads "There are x months and x days left on your trial" where the period is stored in the myfield field:
concat('There are ',getPeriodMonths(myfield),' months and ',getPeriodDays(myfield),' days left on your trial')Where myfield is a field inserted via the Insert Field button in the expression editor.
Explanation:
- getPeriodMonths and getPeriodDays extract the number of months and days in a period, taking into account overflow of days into months (e.g. 35 days = 1 month and 5 days).
- concat allows you to put text strings together:
- 'There are '
- x
- ' months and '
- x
- ' days left on your trial'
Break down a period into months and days (advanced)
We can make the previous example even better by adding a conditional that checks whether there actually is a month left. If there is no month left, you could end up with "There are 0 months and 20 days left on your trial."
It would be better to address this by adding a conditional, like so:
if(lt(getPeriodMonths(myfield),1),concat('There are ',getPeriodDays(myfield),' days left on your trial'),concat('There are ',getPeriodMonths(myfield),' months and ',getPeriodDays(myfield),' days left on your trial'))Not really readable, so use the Pretty print button in the expression editor to make it more readable:
Pretty Print Example
if(
lt(
getPeriodMonths(myfield),
1
),
concat(
'There are ',
getPeriodDays(myfield),
' days left on your trial'
),
concat(
'There are ',
getPeriodMonths(myfield),
' months and ',
getPeriodDays(myfield),
' days left on your trial'
)
)Explanation:
- if(cond,a,b) will display a if the condition cond is true and b if it is not true.
- lt(a,b) returns true when a is lower than b, otherwise it returns false.
For more information about these functions, check out List of functions.
Display the first day of the next month without using the first/last functions (advanced)
This was written before the firstDateOfMonth, lastDateOfMonth and lastDayOfMonth functions existed and this can be solved much more easily (see above). But we'll leave it here as it's a good example of how creative you can get with Smart Flows' functions.
If you want to display a line that shows the date of the first day of the next month, like Thursday 1 December, 2016 when it's currently November 2016:
formatDate( subDaysFromDate( addMonthsToDate( currentDate(), 1 ), formatDate( addMonthsToDate( currentDate(), 1 ), 'd' )-1 ), 'EEEE d MMMM, yyyy')
Not really readable, so use the Pretty print button in the expression editor to make it more readable:
formatDate(
subDaysFromDate(
addMonthsToDate(
currentDate(),
1
),
formatDate(
addMonthsToDate(
currentDate(),
1
),
'd'
)-1
),
'EEEE d MMMM, yyyy'
)Explanation:
- formatDate(addMonthsToDate(currentDate(),1),'d') returns what day it will be one month from now. This may differ from the current day when you try to add one month to January 30th - as then you would get February 28th or 29th. So we add one month to today and then ask Smart Flows to give us what day it will be in one month.
- Once we have that day, we subtract 1 from it. So if our previous result gives December 15th, we end up with the number 14.
- subDaysFromDate(addMonthsToDate(currentDate(),1), ... ) where ... is the result we got earlier (14 in our example). This series of functions will add one month to today and then subtract a number of days from that date. In our example, it will subtract 14 days from that date.
- formatDate( ... , 'EEEE d MMMM, yyyy') where ... is all of the previous, returns the date in the correct formatting. More info on Date & time patterns.
Automatically put Christmas wishes in a document (advanced)
If you want your document to read "Merry Christmas and a happy new year!", but only between December 5th and January 10th of every year:
if(or(and(eq(formatDate(currentDate(),'M'),12),gt(formatDate(currentDate(),'d'),4)),and(eq(formatDate(currentDate(),'M'),1),lt(formatDate(currentDate(),'d'),11))),'Merry Christmas and a happy new year!','')
Not really readable, so use the Pretty print button in the expression editor to make it more readable:
Pretty Print Xmas Wishes Example
if(
or(
and(
eq(
formatDate(
currentDate(),
'M'
),
12
),
12
),
gt(
formatdate(
currentdate(),
'd'
),
4
)
),
and(
eq(
formatdate(
currentDate(),
'M'
),
1
),
lt(
formatdate(
currentDate(),
'd'
),
11
)
),
),
Merry Christmas and a happy new year!',
)Explanation:
- eq(formatDate(currentDate(),'M'),12) checks whether today's month is 12 (December).
- gt(formatDate(currentDate(),'d'),4) checks whether today's day is greater than 4 (so 5 or higher).
- These 2 checks are put together in an and(...) that requires both of them to be true.
- eq(formatDate(currentDate(),'M'),1) checks whether today's month is 1 (January).
- lt(formatDate(currentDate(),'d'),11) checks whether today's month is lower than 11 (so 10 or lower).
- These 2 checks are put together in an and(...) that requires both of them to be true.
- Both and() are put in an or(...) to check whether one of them is true.
- if(or(...),'Merry Christmas and a happy new year!','' returns the sentence if the or(...) is true, and the empty string '' if it's not true.