0

I received the following error creating a formula within the Visual Flow formula editor after selecting he 'Check Syntax' button. Output should be a Date/Time value:

The formula expression is invalid: Syntax error. Missing ')'

For the following statement:

IF(
    {!$Record.Date_Time_Field_1__c} < 14:00:00, 
    {!$Record.Date_Time_Field_1__c} + 04:00:00, 
    {!$Record.Date_Time_Field_2__c}
)

This seems fairly straightforward so I am hoping someone can shed some light. Am I missing something related to the Visual Flow formula editor, $Record placeholders or Date/Time values here that results in the error?

2 Answers 2

1

The error here is misleading

The actual issue is that the datetime comparison and addition is incorrect.

It seems to me that for the "condition" part of your IF(), you simply want to see if the time portion of the datetime is before 14:00. Setting aside concerns about handling time zones, you want to use the TIMEVALUE() function. You also need to use TIMEVALUE() for your literal 14:00:00 (and it needs to be enclosed in single or double quotes)

TIMEVALUE({!$Record.Date_Time_Field_1__c}) < TIMEVALUE("14:00:00")

We don't use hh:mm:ss format in datetime addition. Instead, you add a "decimal" type number. Whole numbers are the number of days, and the fractional part represents partial days. To add 4 hours, you want...

{!$Record.Date_Time_Field_1__c} + (4.0/24.0) (or + 1.0/6.0, or roughly + 0.16666667)

0
0

The problem you are having is comparision with right data type.

IF(
    {!$Record.Date_Time_Field_1__c} < DATETIMEVALUE("2005-11-15 17:00:00") , 
    {!$Record.Date_Time_Field_1__c} + 4, 
    {!$Record.Date_Time_Field_2__c}
)

In the if condition, you have to compare with datetimevalue data type. If you want to add, you can add number to datetime field.

Update

To add hours, we can use this:-

Add N hours to a date/time field: Datetimefield__c + (N/24) In these, N is the number of hours

IF(
    {!$Record.Date_Time_Field_1__c} < DATETIMEVALUE("2005-11-15 17:00:00") , 
    {!$Record.Date_Time_Field_1__c} + (4/24), 
    {!$Record.Date_Time_Field_2__c}
)
4
  • datetime + 4 would add 4 days rather than 4 hours
    – Derek F
    Jun 2, 2023 at 12:44
  • Are you sure that 4/24 would return a decimal instead of doing integer division and returning 0?
    – Derek F
    Jun 2, 2023 at 12:49
  • Thanks @DerekF, I have added the hour version as well Jun 2, 2023 at 12:49
  • @DerekF 4/24 worked fine for me. I don't see any issue. Jun 2, 2023 at 13:07

You must log in to answer this question.

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