Hey Kirstynn!
I'll provide some suggested formulas below, but first I'll just chat a bit about field options. Especially when we are new to formulas, we may feel inclined to put everything into separate fields. Whether we need separate fields or not depends a lot on what needs to happen based on the field's value. Some examples:
- If human awareness and action need to be the result (no automations or notifications), you could potentially use one formula text field.
- If you need to count separately the 4, 2, and 2 data, then you would likely need separate fields for each.
Just some food for thought to think about how fields might be consolidated depending on the purpose they serve to your workflow.
Now to the formulas!
To calculate quantity of days between two date fields
Field type: Formula - Duration
Example field label: Days Between
Formula: [Date 2] - [Date 1]
>> Again, ask yourself/your stakeholders, do you need to see this value, or can it be included as a piece of other formula fields.
To calculate whether the quantity of days between two date fields is within n days
Field type: Formula - Checkbox
Formula: If(ToDays([Days Between])>=4 and [Request Type] = "Supplier",true,false)
>> This will return a checked box (true value) at or above 4 days between the two dates.
Combined calculation in a checkbox field
Field type: Formula - Checkbox
Example field label: Send notification
Formula:
var Duration DaysBtwn = [Date 2] - [Date 1];
If($DaysBtwn >= 4 and [Request Type] = "Supplier", TRUE,
$DaysBtwn >= 2 and [Request Type] = "Bidder", TRUE, ...)
Combined calculation in a text field
Field type: Formula - Text
Example field label: Attention
Formula:
var Duration DaysBtwn = [Date 2] - [Date 1];
If($DaysBtwn >= 4 and [Request Type] = "Supplier", "NOTE TO END USER",
$DaysBtwn >= 2 and [Request Type] = "Bidder", "NOTE TO END USER", ...)
>> You could also get fun with this in a Formula - Rich Text to add colors and text formatting
------------------------------
Chelsea Carpenter
------------------------------
