Date formula to display status

[automadien]

Hi there! There are a few formulas out here for similar-ish issues but I can’t find on that matches exactly what I need.

I’m trying to create a formula that shows “Done” if the date is in the past, “Upcoming” if in the future or “In progress” if it’s on the day. The first two are working fine, but I can’t get it to show “In Progress”. It’s driving me insane as I can’t figure out why :frowning:

IF(
  IS_AFTER({Live Date}, TODAY()),
  "✅Done✅",
  IF(
    {Live Date} = BLANK(),
    "🛑DATE NEEDED🛑",
    IF(
        IS_BEFORE({Live Date}, TODAY()
      ),
      "⏰Upcoming⏰",
      "🏃‍♂️In progress🏃‍♂️"
    )
  )
)

[Justin_Barrett]

I suggest reordering the logic. Check for a blank field first, then the before and after options, at which point the only option left is today (in progress). I get the feeling that the test for a blank field being in the middle of the formula might be messing things up.

For the blank field check, I’m using the shortcut that just checks the field value: an empty field is equivalent to False, while a non-empty field is equivalent to True. For the before/after checks, I’m using the > and < operators, which (to me) feel more intuitive than the IS_BEFORE() and IS_AFTER() functions.

IF(
  {Live Date},
  IF(
    {Live Date} < TODAY(),
    "✅Done✅",
    IF(
      {Live Date} > TODAY(),
      "⏰Upcoming⏰",
      "🏃‍♂️In progress🏃‍♂️"
    )
  ), "🛑DATE NEEDED🛑"
)

[automadien]

Hey Justin, thanks for sharing! I’m still not having any joy with this - In Progress still isn’t appearing for events that are happening :frowning:

[Justin_Barrett]

What timezone are you in? Both TODAY() and NOW() calculate their values based on GMT, not local time, so it’s possible that your offset from GMT means that “today” becomes “tomorrow” earlier than you’d like.

Here’s a variation that uses NOW() instead of TODAY(), converting it to your local time using SET_TIMEZONE(), and wrapping it inside the IS_SAME() function. Here’s a variation that uses both in an attempt to solve this problem. Replace “TIMEZONE_SPECIFIER” with the appropriate specifier for your region.

IF(
  {Live Date},
  IF(
    {Live Date} < TODAY(),
    "✅Done✅",
    IF(
      {Live Date} > TODAY(),
      "⏰Upcoming⏰",
      IF(
        IS_SAME(SET_TIMEZONE(NOW(), "TIMEZONE_SPECIFIER"), {Live Date}, "day"),
        "🏃‍♂️In progress🏃‍♂️"
      )
    )
  ), "🛑DATE NEEDED🛑"
)