Date & Time Expressions

Date related functions

You should try to use the isValidDate() function around any date expression to check for nulls when referencing a Date based Field in an Expression.

Returns DateTime, after adding a numeric value to a date field:
addDays([DateField], 1)
addMonths([DateField], 2)
addYears([DateField], 3)

dateDiff([EndDate], [StartDate]) : Returns number of days between two dates. Use the 'datediff' function to calculate the duration of a task based on an End Date minus a Start Date

Date Time Difference (Whole Days): if(isValidDate([StartDate]) && isValidDate([EndDate]), floor(dateDiff([EndDate], [StartDate])), 0)

 

 

For Time Difference in minutes this can be done by multiplying the difference in days by hours and minutes

Time Difference (Minutes): if(isValidDate([StartDate]) && isValidDate([EndDate]), dateDiff([EndDate], [StartDate]) * 24 * 60, 0)

 

Again, if seconds are being captured then using round() or floor() can limit to whole minutes.

 

 

If you would like a mix of hours and days, this could be achieved by combining the following functions:

For Hours: floor(dateDiff([EndDate], [StartDate]) * 24)

For Remaining Minutes: round((dateDiff([EndDate], [StartDate]) * 24 - floor(dateDiff([EndDate], [StartDate]) * 24)) * 60, 0)

 

For Calculation and Charting it would make sense to keep to one Unit of Time but for displaying back in an easy format to Users, the following would give a time difference in the format 2hrs 10mins

string(floor(dateDiff([EndDate], [StartDate]) * 24)) + 'hrs ' + string(round((dateDiff([EndDate], [StartDate]) * 24 - floor(dateDiff([EndDate], [StartDate]) * 24)) * 60, 0)) + 'mins'

 

 

day([DateField]) : Returns day
month([DateField]) : Returns month
year([DateField]) : Returns years
hour([DateField]) : Returns the hour of a date/time field
minute([DateField]) : Returns the minute of a date/time field

dateNow() : Returns the current date into a date field, or an expression expecting a date field

dateTimeNow() : Returns the current date and time into a datetime field

isValidDate([DateField]) : Returns true or false if the field being specified as the parameter holds a valid date field. isValidDate()  is useful in expressions to account for scenarios where dates referenced in expressions may have no value. If you had an expression which added 30 days to [DateField], you need to check that the date field has a value before adding days to it, so that would look like.

if(IsValidDate([DateField]), addDays([DateField], 30), [BlankDate])

If the value in [DateField] is a valid date, then add 30 days to the value in [DateField], otherwise return the value from a hidden Field that has a blank date ([BlankDate]). You could replace the field reference to blank date to ‘2000-01-01T00:00:01Z’ which is the actual value of blank for a date (date fields have to contain a value)

dateParse([String]) : Returns null or the date if the text being specified as the parameter is in a parseable date format – Examples of Valid date formats are '2015-11-30′, '30 Nov 2015'

minDate([DateField1], [DateField2], [DateField3], [DateField4], [DateField5]) : Given a list of dates or date fields, the function returns the earliest date

maxDate([DateField1], [DateField2], [DateField3], [DateField4], [DateField5]) : Given a list of dates or date fields, the function returns the latest date

weekNum([DateField]) : This will return the week of the year that the date in the date field falls into

CreatedDate() : Using this function returns the date for the earliest entry in the activity stream.

UpdatedDate() : Using this function returns the date for the latest entry in the activity stream

Example

A complex example which takes a date time field [TestDateTime] and converts it in to a friendly formatted date in a text field using an expresion as follows 

if(IsValidDate([TestDateTime]),

'' + year([TestDateTime])

+ '-' + IF(month([TestDateTime]) <10, '0', '') + month([TestDateTime])

+ '-' + IF(day([TestDateTime]) <10, '0', '') + day([TestDateTime])

+ ' ' + IF(hour([TestDateTime]) <10, '0', '') + hour([TestDateTime])

+ ':' + IF(minute([TestDateTime]) <10, '0', '') + minute([TestDateTime])

, 'No Date Provided')

The above would check the validity of a date field before then showing the date in the format 

2020-12-07 13:45

Time Related Functions

Expressions can be used to combine a time to a given date on the Record, to calculate the difference between to times and also to set the current time.

TimeDiffString(TimeFieldIdentifier1, TimeFieldIdentifier2, DisplaySeconds (optional)): This will return to a Text Field the difference between two times in a friendly format of hours, minutes and optionally seconds.

Example - If EndTime is 14:40 and StartTime is 2:20 then:

TimeDiffString(EndTime, StartTime, true) returns 12:20:00

TimeDiffString(EndTime, StartTime, false) returns 12:20

TimeDiffTotalSeconds(TimeFieldIdentifier1, TimeFieldIdentifier2): This will return hours and minutes to a Time Field or number of seconds to a Number Field and returns the difference between the two times.

Example - If StartTime is 14:40 and EndTime is 15:40 then:

TimeDiffTotalSeconds(StartTime, EndTime) returns 01:00 to a Time Field

TimeDiffTotalSeconds(StartTime, EndTime) returns 3600 to a Number Field

TimeNowString(DisplaySeconds (optional)): This will return to a Text Field the current time of day (in UTC form) upon the opening of a Record.

Example - If current time of day is 15:30 then:

TimeNowString(true) returns 15:30:00

TimeNowString(false) returns 15:30

TimeNowTotalSeconds(): This will return to a Time or Number Field the current time of day (in UTC form) upon the opening of a Record.

 In the instance of returning to a Number Field the number of seconds past 00:00 will be given.

Combining Date and Time Fields

CombineDateWithTime(DateFieldIdentifier, TimeFieldIdentifier): This will bring together both a Date Field and a Time Field in order to present both in a DateTime Field. An example of such is given below:

Make sure to click the 'Save' button when making any changes in order for them to be added to the next app version. Once you have made all the changes you need to an application you are then ready to publish it to workspace.

Note on Field Dependencies - Read more here

Field dependencies are by definition the other field(s) that are reliant on a given field, usually dependent fields are interlinked to one another by expressions and are indicated by an atom symbol. They are a crucial tool when looking to delete Fields within an App or, when it comes to understanding the structure and data flows of an App.