3

I am trying to modify a formula field that we currently use that looks at a date (Date_Booked__c) and based on the MONTH and YEAR returns a text string along the lines of Q1-2017, Q2-2017, etc.

We are going to a custom fiscal year starting in January that will start in February but for the first year Q1 will start in January and run for 4 months (pretty standard.) But I need to be able to keep previously years on the old quarter breakdown. I can't seem to get past the error: "Incorrect parameter type for operator '<'. Expected Date, received Text"

I have tried inserting the VALUE function, DATEVALUE, TEXT, etc. and no matter what still receive the error.

Would be grateful if someone could look at this and let me know what I'm doing wrong.

IF(YEAR(Date_Booked__c < 2018
CASE(MONTH( Date_Booked__c ), 
1,"Q1", 
2,"Q1", 
3,"Q1", 
4,"Q2", 
5,"Q2", 
6,"Q2", 
7,"Q3", 
8,"Q3", 
9,"Q3", 
10,"Q4", 
11,"Q4", 
12,"Q4","None") &"-"&TEXT(YEAR(Date_Booked__c )),

CASE(MONTH( Date_Booked__c ), 
1,"Q4", 
2,"Q1", 
3,"Q1", 
4,"Q1", 
5,"Q2", 
6,"Q2", 
7,"Q2", 
8,"Q3", 
9,"Q3", 
10,"Q3", 
11,"Q4", 
12,"Q4","None") &"-"&TEXT(YEAR(Date_Booked__c )),"None"
Improve this question

2 Answers 2

Reset to default
1

You forgot a closing parentheses, and a comma:

IF(YEAR(Date_Booked__c) < 2018,

You probably also have another stray parentheses floating in there somewhere to compensate for this one.

As an aside, it's not really necessary to have one of the quarters typed out fully; you could just as easily use the default for that purpose.

CASE(MONTH( Date_Booked__c ), 
1,"Q1", 
2,"Q1", 
3,"Q1", 
4,"Q2", 
5,"Q2", 
6,"Q2", 
7,"Q3", 
8,"Q3", 
9,"Q3",
"Q4")
Improve this answer
1

You're missing a close parenthesis and a comma in the first line, for starters:

IF(YEAR(Date_Booked__c < 2018

should instead be:

IF(YEAR(Date_Booked__c) < 2018,

You also close the IF condition early and have a spare parenthesis at the end. It's much easier to catch these errors if you use a bit of indentation to indicate logical nesting levels.

Here's your full formula annotated and indented:

IF(
    YEAR(Date_Booked__c < 2018 // <-- missing comma and close parenthesis for YEAR
    CASE(
        MONTH( Date_Booked__c ), 
        1,"Q1", 2,"Q1", 3,"Q1",
        4,"Q2", 5,"Q2", 6,"Q2",
        7,"Q3", 8,"Q3", 9,"Q3",
        10,"Q4", 11,"Q4", 12,"Q4",
        "None"
    ) & "-" & TEXT(YEAR(Date_Booked__c )),
    CASE(
        MONTH( Date_Booked__c ), 
        1,"Q4", 2,"Q1", 3,"Q1",
        4,"Q1", 5,"Q2", 6,"Q2",
        7,"Q2", 8,"Q3", 9,"Q3",
        10,"Q3", 11,"Q4", 12,"Q4",
        "None"
    ) & "-" & TEXT(YEAR(Date_Booked__c )),"None" // <-- missing close parenthesis for IF

Here's a corrected version:

IF(
    YEAR(Date_Booked__c) < 2018,
    CASE(
        MONTH(Date_Booked__c), 
        1,"Q1", 2,"Q1", 3,"Q1",
        4,"Q2", 5,"Q2", 6,"Q2",
        7,"Q3", 8,"Q3", 9,"Q3",
        10,"Q4", 11,"Q4", 12,"Q4",
        "None"
    ) & "-" & TEXT(YEAR(Date_Booked__c)),
    CASE(
        MONTH(Date_Booked__c), 
        1,"Q4", 2,"Q1", 3,"Q1",
        4,"Q1", 5,"Q2", 6,"Q2",
        7,"Q2", 8,"Q3", 9,"Q3",
        10,"Q3", 11,"Q4", 12,"Q4",
        "None"
    ) & "-" & TEXT(YEAR(Date_Booked__c))
)
Improve this answer
2
  • Thanks for the quick response - It's always those pesky commas, and parnethesis!
    – megachuckmc
    Commented Dec 13, 2017 at 22:19
  • 1
    @meg Like I sad, indentation is your friend in catching this kind of stuff. If you don't indent, soon you will repent.
    – Adrian Larson ♦
    Commented Dec 13, 2017 at 22:20

You must log in to answer this question.

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