Skip to main content
Question

Text Field showing different values depending on the calculation

  • February 8, 2019
  • 0 replies
  • 151 views
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")
    This topic has been closed for replies.

    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.
    • KatrinaE
    • Author
    • Registered
    • February 8, 2019
    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.
    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")
    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]
    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. 

    Terms & ConditionsAccessibility statement