Discussions

 View Only
Expand all | Collapse all

Time Duration Formula

Raymond Marlon Tiangco

Raymond Marlon Tiangco08-02-2017 09:58

Raymond Marlon Tiangco

Raymond Marlon Tiangco08-03-2017 15:22

  • 1.  Time Duration Formula

    Posted 08-01-2017 15:25
    I have five (5) Date / Time type fields in my App. With a Handling Time Field (HT) - Formula Duration Type

    Example:
    T1: 07-27-2017 10:00 PM
    T2: 07-28-2017 08:00 AM
    T3: 07-28-2017 08:30 AM
    T4: 07-28-2017 04:00 PM
    T5: 07-28-2017 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. 


  • 2.  RE: Time Duration Formula

    Posted 08-01-2017 15:28
    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.


  • 3.  RE: Time Duration Formula

    Posted 08-01-2017 15:34
    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: 07-27-2017 10:00 PM
    T2: 07-28-2017 08:00 AM
    T3: 
    T4: 
    T5: 07-28-2017 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


  • 4.  RE: Time Duration Formula

    Posted 08-02-2017 09:58
    Do you have any updates with my concern? Please advise.


  • 5.  RE: Time Duration Formula

    Posted 08-02-2017 18:24
    try  this

    var datetime Start = Min([T1], [T2], [T3], [T4], [T5]);
    var datetime End = Max([T1], [T2], [T3], [T4], [T5]);

    $End - $Start


  • 6.  RE: Time Duration Formula

    Posted 08-03-2017 13:36
    Where will I enter those formulas?
    Will I replace this formulas I made?
    ([T5]-[T4])+([T3]-[T2])

    Please advise.


  • 7.  RE: Time Duration Formula

    Posted 08-03-2017 13:41
    I suggest that you make a new formula duration field and try to get the new field working how you want it to.


  • 8.  RE: Time Duration Formula

    Posted 08-03-2017 14:06
    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.


  • 9.  RE: Time Duration Formula

    Posted 08-03-2017 14:46
    Make a Formula Duration field:

    If(IsNull([T3-time sent request]),
    ([T5-time completed]-[T2-time start process]),
    ([T5-time completed]- [T4-time received request])+([T3-time sent request]-[T2-time start process]))

    Where your 'T" fields are all Date/Time types


  • 10.  RE: Time Duration Formula

    Posted 08-03-2017 15:22
    This worked! Thanks!


  • 11.  RE: Time Duration Formula

    Posted 08-04-2017 15:19
    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!


  • 12.  RE: Time Duration Formula

    Posted 08-08-2017 16:57
    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])


  • 13.  RE: Time Duration Formula

    Posted 08-09-2017 14:36
    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!