I have five (5) Date / Time type fields in my App. With a Handling Time Field (HT)  Formula Duration Type
Example:
T1: 07272017 10:00 PM
T2: 07282017 08:00 AM
T3: 07282017 08:30 AM
T4: 07282017 04:00 PM
T5: 07282017 04:30 PM
HT = ([T5][T4])+([T3][T2]) = 1 Hour
Sometimes I don't need to enter anything for both T4 and T3 but I still need to compute for HT. And when that happens I don't see any value for HT.
What am I suppose to do with the formula so it could still compute for HT even without T4 and T3?
Please advise.
Example:
T1: 07272017 10:00 PM
T2: 07282017 08:00 AM
T3: 07282017 08:30 AM
T4: 07282017 04:00 PM
T5: 07282017 04:30 PM
HT = ([T5][T4])+([T3][T2]) = 1 Hour
Sometimes I don't need to enter anything for both T4 and T3 but I still need to compute for HT. And when that happens I don't see any value for HT.
What am I suppose to do with the formula so it could still compute for HT even without T4 and T3?
Please advise.
 124 Points
Posted 2 years ago
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,448 Points
Can you give an example of the data entry situations for missing data and the result you want? You need to first be able to say the formula is regular english words, and then we can help turn that into a formula.
 124 Points
I'm just entering the each Date / Time Field Manually
T1 = Time Received
T2 = Time Start Process
T3 = Time Sent Request
T4 = Time Received Request
T5 = Time Completed
HT = Handling Time
So for example: I don't need to make a request so T3 and T4 is not needed anymore to compute for the Total handling time based on the formula given.
HT = ([T5][T4])+([T3][T2])
T1: 07272017 10:00 PM
T2: 07282017 08:00 AM
T3:
T4:
T5: 07282017 08:15 AM
HT should be equal to 00:15 or 15 minutes but it doesn't show any value if T3 and T4 is blank
T1 = Time Received
T2 = Time Start Process
T3 = Time Sent Request
T4 = Time Received Request
T5 = Time Completed
HT = Handling Time
So for example: I don't need to make a request so T3 and T4 is not needed anymore to compute for the Total handling time based on the formula given.
HT = ([T5][T4])+([T3][T2])
T1: 07272017 10:00 PM
T2: 07282017 08:00 AM
T3:
T4:
T5: 07282017 08:15 AM
HT should be equal to 00:15 or 15 minutes but it doesn't show any value if T3 and T4 is blank
 124 Points
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,448 Points
try this
var datetime Start = Min([T1], [T2], [T3], [T4], [T5]);
var datetime End = Max([T1], [T2], [T3], [T4], [T5]);
$End  $Start
var datetime Start = Min([T1], [T2], [T3], [T4], [T5]);
var datetime End = Max([T1], [T2], [T3], [T4], [T5]);
$End  $Start
 124 Points
Where will I enter those formulas?
Will I replace this formulas I made?
([T5][T4])+([T3][T2])
Please advise.
Will I replace this formulas I made?
([T5][T4])+([T3][T2])
Please advise.
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,448 Points
I suggest that you make a new formula duration field and try to get the new field working how you want it to.
 124 Points
I tried to create new formula duration field and still not able to get the result I need based on this the formula I gave which is HT = ([T5][T4])+([T3][T2]).
Kindly advise.
Kindly advise.
 340 Points
Make a Formula Duration field:
If(IsNull([T3time sent request]),
([T5time completed][T2time start process]),
([T5time completed] [T4time received request])+([T3time sent request][T2time start process]))
Where your 'T" fields are all Date/Time types
If(IsNull([T3time sent request]),
([T5time completed][T2time start process]),
([T5time completed] [T4time received request])+([T3time sent request][T2time start process]))
Where your 'T" fields are all Date/Time types
 124 Points
With the same date & time field represented by T01, T02, etc.
How can you make an IF equation if any of the following date&time field (represented by T01, T02, etc) can be NULL anytime from this equation?
WT = ([T02][T01])+([T05][T04])+([T07][T06])+([T09][T08])+([T11][T10])+([T13][T12])+([T15][T14])
WT = Total Time Duration
Can you please advise.
Thanks!
How can you make an IF equation if any of the following date&time field (represented by T01, T02, etc) can be NULL anytime from this equation?
WT = ([T02][T01])+([T05][T04])+([T07][T06])+([T09][T08])+([T11][T10])+([T13][T12])+([T15][T14])
WT = Total Time Duration
Can you please advise.
Thanks!
 340 Points
When you write a formula in the field, you can select a function. There is and explanation and example of the use of the function displayed. Or click ?Help at the top of the screen and choose Formula functions for a complete guide.
You can wrap each pair with the Nz() function:
Nz([T02]  [T01]) +
Nz([T05]  [T04]) +
Nz([T07]  [T06]) +
Nz([T09]  [T08]) +
Nz([T11]  [T10]) +
Nz([T13]  [T12]) +
Nz([T15]  [T14])
You can wrap each pair with the Nz() function:
Nz([T02]  [T01]) +
Nz([T05]  [T04]) +
Nz([T07]  [T06]) +
Nz([T09]  [T08]) +
Nz([T11]  [T10]) +
Nz([T13]  [T12]) +
Nz([T15]  [T14])
 124 Points
Hi Lisa, you're a genius! Thank you for showing me how to use Nz function. It worked!
I really appreciate you for helping me. Have a good day!
I really appreciate you for helping me. Have a good day!
Related Categories

App builders
 977 Conversations
 47 Followers

Formulas & functions
 3064 Conversations
 84 Followers

Ask about Quick Base functionality
 943 Conversations
 67 Followers