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

### 3 Replies

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

• Thank you! I was able to get those fields/formulas created. I know have one more question. In my report I was able to get the number of contracts per month that were yes or no to KPI.... Am I able to get a report that shows the different types of request with the yes or no per month?

------------------------------
Kirstynn Rowe
------------------------------
• I believe if you use the 'Series' option in your bar chart and select type of request, it will break it down by type

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