Forum Discussion

KirstynnRowe's avatar
KirstynnRowe
Qrew Member
2 years ago

Duration Formula

Hello. I use quickbase to track contract request. There are a few different types and based off the type we have a target to get them sent out in. So I have a Received Date Field and a Date Issued Field. I need a formula to tell me if a certain type of request (New Supplier, Existing) was issued with in 4 days, another to tell me if a different type of request (Bidder) was issued in 2 days, and one to tell me if another type of request (Convo) was issued in 2 days. I can figure out how to get a field at the end of my table that says how many days is between the received date and the issued date but I am struggling to determine how to get it to mark if it hit the target based of the request type. 



------------------------------
Kirstynn Rowe
------------------------------
  • ChelseaCarpent2's avatar
    ChelseaCarpent2
    Quickbase Staff
    2 years ago

    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
    ------------------------------