0

I have this formula which checks on a picklist field not equal to certain values and if the date is less than today, this formula field should have a value of Overdue or else it should take the value of another field in the same object. As of now this formula doesn't show any errors and it always shows the value of the Status field even when the date is set in the past. Here is the formula -

If (
OR(
TEXT(Status) != "Completed",
TEXT(Event_Status) != "Canceled"
)
&&
(ActivityDate+DurationInMinutes) < TODAY(),
"Overdue",
TEXT(Status)

UPDATE -

If (
OR(
TEXT(Event_Status__c) != "Completed", 
TEXT(Event_Status__c) != "Canceled"
)
&&
(ActivityDateTime+(DurationInMinutes/1440)) < NOW(),
"Overdue",
TEXT(Event_Status__c)
)
4
  • This looks like a formula field and not a validation rule. A validation rule must return a boolean (true/false) value.
    – Derek F
    Mar 29, 2022 at 21:21
  • Sorry, I changed the title, it is a formula field, any idea why the above is not working. Mar 29, 2022 at 21:50
  • ActivityDate is a Date field, not a DateTime field
    – cropredy
    Mar 29, 2022 at 22:02
  • @cropredy I made a few changes to the field and put it as an update to the original post. If I check on the datettime now it works, but it doesn't work when I combine it with the values of the Event_Status field. Mar 29, 2022 at 22:07

1 Answer 1

1

I believe you want:

If (
  AND( /* Event status is neither Completed or Cancelled */
   TEXT(Event_Status__c) != "Completed", 
   TEXT(Event_Status__c) != "Canceled"
  )
   && /* due date is before today */
  (ActivityDateTime+(DurationInMinutes/1440)) < NOW(),
  "Overdue",  /* then overdue */
  TEXT(Event_Status__c)
 )
3
  • Thanks Cropredy Mar 29, 2022 at 22:18
  • commenting your formulas as you write them often resolves issues :-)
    – cropredy
    Mar 29, 2022 at 22:20
  • Sure, Agree to that Mar 30, 2022 at 16:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .