Calculated Field Not Getting Updated

[ConnieMaldonado]

Hello - I have a Status field (Type Color) which is Green, Yellow or Red based on whether the job has started (Start Time) and finished (End Time). I have a formula for the status field. When I edit a record and change the start time and/or end time, the Status does not get updated. Also, I sent the initial value for Status to “Red”, so why doesn’t red display for every record in the table?

I’m using a Table View. Do I need to change the view type? I hate to do that as I need to display the records in such a way that users can find them.

[WillowMobileSys]

You need to add square brackets around your column names End Time and Start Time.

AID #1: To help simplify the expression you can use the function ISNOTBLANK().

AID #2: Take a look at the IFS() expression. This will help make it easier to read.

I hope this helps!!

[ConnieMaldonado]

That seems to have worked! It must have been the around the field name. I thought it was something more complicated, like it needed to be a virtual column. Thanks; I appreciate it!

[WillowMobileSys]

You are welcome!!

[Steve · Accepted solution]

Your expression, reformatted for clarity:

IF(
  AND(
    ISBLANK(End Time),
    NOT(ISBLANK(Start Time))
  ),
  "Yellow",
  IF(
    AND(
      NOT(ISBLANK(Start Time)),
      NOT(ISBLANK(End Time))
    ),
    "Green",
    "Red"
  )
)

With @WillowMobileSystems’ suggestions:

IFS(
  AND(
    ISBLANK([End Time]),
    ISNOTBLANK([Start Time])
  ),
    "Yellow",
  AND(
    ISNOTBLANK([Start Time]),
    ISNOTBLANK([End Time])
  ),
    "Green",
  TRUE,
    "Red"
)

Further simplified:

IFS(
  ISBLANK([Start Time]),
    "Red",
  ISBLANK([End Time]),
    "Yellow",
  TRUE,
    "Green",
)

[ConnieMaldonado]

Wow that really is simplified. Thank you!

[WillowMobileSys]

Very nice!