 ShowAll Questionssorted byDate Posted rock_chalk

# Formula Syntax Errors

Hi,

Can someone tell me what I'm doing wrong with this formula?  I just want to create categories that I can group start times by in a report:

IF(TRIM(LEFT(Start_Time__c, 1)) = "7", "7 AM", "",
IF(TRIM(LEFT(Start_Time__c, 1)) = "8", "8 AM", "",
IF(TRIM(LEFT(Start_Time__c, 1)) = "9", "9 AM", "" )))

If I close the first statement it works correctly, but as soon as I add on I get an error.

Any help would be appreciated!

Thanks,

Scott len123
I am not sure what is the problem with your formula (what is the error? it will help) but I can give you a formula I used for similar thing. I wanted to take the hour from the a date/time field. This is the formula and it's working:

MID ( TEXT ( CreatedDate+0.125 ), 12, 2)

I added 0.125 so that the time will be according to my time zone. This value can change according to the time zone. rock_chalk
Thanks for your response.  The error I receive is when I try to extend the formula by removing the parenthesis at the end of the first line and add a comma.  For some reason, the comma is seen as another parameter for the IF function.  This is the error:

Error: Incorrect number of parameters for function IF(). Expected 3, received 4

I've tried all the different combinations I can think of but they all result in errors.  I need to create categories for times from 7AM to 7PM, so I have to be able to extend the formula.

Scott NPM

Try

IF(TRIM(LEFT(Start_Time__c, 1)) = "7", "7 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "8", "8 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "9", "9 AM", "" ))) Kent Manning

Are you a Kansas Jayhawk?

You have nested IF statements, so you have an extra " ", in the first and second if statements.  So if you remove them, then you should eliminate the syntax errors.

IF(TRIM(LEFT(Start_Time__c, 1)) = "7", "7 AM", "", <---Remove the extra "",
IF(TRIM(LEFT(Start_Time__c, 1)) = "8", "8 AM", "",<---Remove
IF(TRIM(LEFT(Start_Time__c, 1)) = "9", "9 AM", "" )))

Should look like this:

IF(TRIM(LEFT(Start_Time__c, 1)) = "7", "7 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "8", "8 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "9", "9 AM", "" )))

Also, you might consider using a CASE statement in place of the nested if statements.

Hope that helps. rock_chalk
Thanks to both, I appreciate your help.

You bet, Kansas Jayhawk through and through! Lori_
Don't remove the parenthesis.  You need add the condition and a comma before the "" and another parenthesis for each new condition; see how I added the red.

IF(TRIM(LEFT(Start_Time__c, 1)) = "7", "7 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "8", "8 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "9", "9 AM",
IF(TRIM(LEFT(Start_Time__c, 1)) = "10", "10 AM", "" ))))

A CASE statement may be easier.  To add new values, just add the red line.
CASE( TRIM(LEFT(Start_Time__c, 1)) ,
"7", "7 AM",
"8", "8 AM",
"9", "9 AM",
"10", "10 AM",
"")