Text Field showing different values depending on the calculation | Discussions

Expand all | Collapse all

Text Field showing different values depending on the calculation

  • 1.  Text Field showing different values depending on the calculation

    Posted 02-08-2019 01:29
    I want a field to show value of "LT1" if it is less than 1 day late than the expected start of the program and other values depending on how many days it is late, I have this formula but it doesn't show the values when I test it

    If(
    not IsNull([Started]),"NA",([Active Dif Calc]>1),"LT1",([Active Dif Calc]>7),"LT7")


  • 2.  RE: Text Field showing different values depending on the calculation

    Posted 02-08-2019 03:42
    The IF conditions are evaluated in the sequence in your formula. So for starters you need to test for the larger values first and then work your way down to the smaller values.

    If(
    not IsNull([Started]),"NA",
    [Active Dif Calc]>7,"LT7",
    [Active Dif Calc]>1,"LT1")

    Note that is not necessary to clutter up your formula with extra brackets. It just makes it harder to read.

    I do not know what type of field

    [Active Dif Calc]

    So I don�t know for sure if that formula will work.


  • 3.  RE: Text Field showing different values depending on the calculation

    Posted 02-08-2019 03:55
    It worked! I have another thing to add to this though.  I'm trying to add a formula to this existing formula that will yield a value of "E1", if the start date which is a date/time field is less than one day away.  I tried to use ">" but it's not allowed for date time field.


  • 4.  RE: Text Field showing different values depending on the calculation

    Posted 02-08-2019 04:09
    Maybe this is what you want

    If(
    not IsNull([Started]),"NA",
    [Active Dif Calc]>7,"LT7",
    [Active Dif Calc]>1,"LT1",
    Now()-[Start Date] < Hours(24), "E1")


  • 5.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 12:50
    This just yields a result of �NA�. What I failed to include in this is that I wanted NA if the project has started on time. Right now it only yields NA because the project�s �Started� field is not null even if the project has been started late.


  • 6.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 12:52
    Please post the formula for

    [Active Dif Calc]


  • 7.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 13:04
    (ToDays(ToDate([Started]) - ToDate([Start])))


  • 8.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 13:05
    (ToDays(ToDate([Started]) - ToDate([Start])))


  • 9.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 16:14
    Perhaps it is mainly the sequence if the logic checks which is the problem.  But before I can answer we seem to have three fields and I'm not understanding the difference.

    [Start Date] 
    [Start]
    [Started]


  • 10.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 16:33
    Sorry about that, I just changed the field names.  I just have Start and Started.  Start is the assigned start date and started is when they actually started the project 


  • 11.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 16:50
    Can you please say the logic that you want in words and then I can convert that to a formula.  Please start with  7 days late and then 1 day late and then on time and then early and then not started to be sure to cover all time periods from late to early.


  • 12.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 17:00
    So I want a field that will tell me if a task has been started or not on time.  I want it to yield a result that will say "NA" if Active Dif Calc is equal to 0, and then if Active dif Calc is  less than 7 days but not equal to 1 it will say "L7".  And then if it is less than 1 day, it will say "L1".  Start and Started are both date/time fields.

    And if possible, I want to add E1 if the difference between start and started dates are less than 24 hours.

    I hope that makes sense.  I've been trying to figure this out but no luck


  • 13.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 17:55
    Try this version

    If( 
    IsNull([Started]) and Today() <  [Start],  "Past Start date but not started,
    [Active Dif Calc]>=7,"LT7", 
    [Active Dif Calc]>=1,"LT1", 
    [Active Dif Calc]=0, "N/A")


    You have asked for the result to be "N/A" if the date that the job was started was equal to the date the job was supposed to start, and then also asked to have it read E1 if the job was started a few hours early but that could be on the same day as it was supposed to start, so that would calculate to "N/A". 

    ie the N/A and E1 logic you have asked for does not make sense to me.  You may have to illustrate with an example. 



  • 14.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 19:28
    Right now, it's showing these results LT7 shows up if a number is greater than 7 


  • 15.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 19:34
    If( 
    IsNull([Started]) and Today() <  [Start],  "Past Start date but not started,
    [Active Dif Calc] <7 and [Active Dif Calc]>1,"LT7", 
    [started]-[start] > hours(0) and [started]-[start] < Hours(24), "L1",
    [Active Dif Calc]=0, "N/A")


  • 16.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 19:39
    For this part of the formula If( 
    IsNull([Started]) and Today() <  [Start], the "<" won't work because the fields are date/time


  • 17.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 19:45
    IsNull([Started]) and Today() <  ToDate([Start]),


  • 18.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 20:59
    This worked the way I wanted it to show.  Thank you!!!!!!


  • 19.  RE: Text Field showing different values depending on the calculation

    Posted 02-23-2019 21:00
    Great,
    Thx for letting me know.