Customize

Calculate the Number of Days

Summary

Calculate the number of days between the start date and the end date.

The results can be directly entered into the field or referenced from other actions.

How to use

Specify the “Start Date” and “End Date” as a date or date and time. You can also specify the values of date fields and datetime fields using expressions.

If a date field value is specified, the date is based on the time zone of the user opening the app. The time part is not included in the calculation.

If the end date is in the past compared to the start date, the result will be a negative value.

To set the result of the calculation to another field, enter the target field into the parameter “Target Field (Optional)”.

Use formulas when using the results for other actions without obtaining the results immediately.

When you execute an action to run “Calculate the Number of Days”, the result of the calculation is stored internally. You can get the saved results by using a formula, for example “=$1'.

If incorrect dates are set for the start or end date, an error will occur when executing the action. In this case, the result of the action will be NaN (indicating that it is not a number).

Parameters

Set Start Date, End Date, and Target Field.

Start Date

Specify the start date.

To access the value of a field:

Example: =Date_2

insert an equals sign “=” before the field code, as in the example.

To specify the current date, write as follows:

=today()

End Date

Specify the end date.

Like the start date, it is possible to reference the values of date fields or datetime fields using expressions, or to specify the current date.

Target Field (Optional)

Use this parameter to set the result of the calculation to another field. You can leave this parameter empty to use the calculation result in another action without setting it to any field.

The value is provided as a numeric figure without any accompanying unit.

If you select a field inside a table, all rows of that table will be processed. For exceptions: See (*1)

*1:Only the affected rows will be processed if you set a condition that applies only to some table rows (such as “When Fields Value Changed”).

This parameter accepts the following field types:

  • Text
  • Number
  • Text area
  • Rich text

Compatibility with krewSheet

This action is designed to work seamlessly with krewSheet.

However, the following restrictions apply to the parameter “Target Field (Optional)":

  • Cannot be set to a field obtained by a Lookup.
  • The following field types can be set when a record is saved (e.g. when you select the Condition “Before Record Saved Excluding Deleted"): If you want to gain more technical knowledge, refer to the krewSheet documentation .
    • Fields not included in the krewSheet view’s columns
    • Fields set to read-only in krewSheet
    • Rich text field