Duration Formula

Question

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

MikeTamoush · Answer

You can do this all in one field/formula, or multiple. For clarity, lets use 2 fields.

The first field would be the Duration. [Duration Field] = [Received Date]-[Date Issued]. This gives your number of days. Note that you may need to add a day to this depending on how you want it to count. For example, June 4-June 3, is that 1 day or 2? QB will say it is one, so if you want it to read as to append + Days(1) to the end of the formula.

The second field is a check. A formula checkbox for example would be fine.
[Was Target Hit Checkbox] =

var number DurationField = ToDays([Duration Field]) //QB sees days as a numeric value, which you need, versus the original field which QB sees as a duration and doesnt do well in math functions

//now a formula to check the box
If(
([Type of Request]="Bidder" or [Type of Request]="Convo") and $DurationField<=2, true,
([Type of Request]="New" or [Type of Request]="Supplier" or [Type of Request]="Existing") and $DurationField<=4, true
)

------------------------------
Mike Tamoush
------------------------------

Sign up

Login to the community