Whether you're calculating task durations, determining intervals between events, or simply trying to display a time difference in a user-friendly format, precision is crucial. As the backbone of countless operations, the manipulation of date and time is a staple in the world of data management and reporting. In this comprehensive guide, we delve deep into a suite of functions specifically crafted for date and time differences. Armed with these tools, you'll navigate time-based calculations with confidence, ensuring accuracy and clarity in every task.
Date Time Manipulation: Adding or Subtracting Time
To ensure accuracy when manipulating date or datetime fields, use the isValidDate() function around any date expression. This checks if the date is valid and not null.
Integers Manipulation:
- Years: addYears([DateField], 3) - Adds 3 years to the Date field.
- Months: addMonths([DateField], 2) - Adds 2 months.
- Days: addDays([DateField], 1) - Adds 1 day.
- Hours: addhours([DateTimeField], -1) - Deducts 1 hour.
- Minutes: addminutes([DateTimeField], -2) - Deducts 2 minutes.
- Seconds: addseconds([DateTimeField], -3) - Deducts 3 seconds.
- Milliseconds: addmilliseconds([DateTimeField], -4) - Deducts 4 milliseconds.
Note: Positive values add time, while negative values subtract.
Fractional Manipulation:
These functions allow more granular adjustments:
- Days: adddaysfraction([DateField], 1.5) - Adds 1 day and 12 hours.
- Hours: addhoursfraction([DateTimeField], -1.25) - Deducts 1 hour and 15 minutes.
- Minutes: addminutesfraction([DateTimeField], -2.75) - Deducts 2 minutes and 45 seconds.
- Seconds: addsecondsfraction([DateTimeField], -3.5) - Deducts 3 seconds and 30 milliseconds.
Date/Time Difference
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
datetimediff([EndDate], [StartDate], ['days'|'hours'|'minutes'|'seconds'|'milliseconds'])
Returns the difference between the two DateTime values as a fractional number of days, hours, minutes, seconds or milliseconds. Written e.g. as `datetimediff([Date1], [Date2], 'seconds')`. If Date2 comes after Date1, the returned value is negative; otherwise it is positive. The third parameter can be a constant, a field reference, or any other sub-expression. If the third parameter is omitted it defaults to `'minutes'` (that's the behaviour of the `datetimediff` function as it exists today.) If the third parameter's value is unrecognised, this function returns the value `0`.
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
Return Date/Time
- day([DateField]) : Returns day
- weekNum([DateField]) : Returns Week Number
- month([DateField]) : Returns month
- year([DateField]) : Returns years
- hour([DateTimeField]) : Returns the hour of a date/time field
- minute([DateTimeField]) : Returns the minute of a date/time field
- second([DateTimeField]) : Returns the Second of a date/time field
- millisecond([DateTimeField]) : Returns the Millisecond of a date/time 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
Record Specific Functions
- 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
- 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
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.
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
Creating a Date and Time Field Value
datetimefromparts(<year>, <month>, <day>, [<hour>], [<minute>], [<second>], [<millisecond>])
Constructs a DateTime out of whole year, month, day, hour, minute, second and millisecond components. Any fractional parts to the numbers are truncated. The millisecond, second, minute and hour arguments are optional; if omitted they will be taken as 0. As with all other Date and DateTime handling the time zone is UTC. If any part is outside of the allowed range, this function returns `null`.
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.
Comments
0 comments
Article is closed for comments.