Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
rock_chalkrock_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
len123len123
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_chalkrock_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
NPMNPM

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 ManningKent 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_chalkrock_chalk
Thanks to both, I appreciate your help.
 
You bet, Kansas Jayhawk through and through!
Lori_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",
  "")