In an custom Oject, that is filled with external market development data I got a picklist field "Month" (Picklist: 1,2,3,4...12) and a picklist field "Year" (picklist: 1990, 1991, 1992... 2020). Each record represents a value for a certain calendar Month. How is it possible to get Salesforce to know that "Year = 2001" and "Month = 5" in one record the period "May 1st 2001 till May 31st 2001" represents. I Need to have Reports and Dashboards showing the market development?


You can follow below steps to create a new Date type formula field

  1. Go to Setup -> Create -> Object
  2. Click on Create new Field
  3. Select Formula from list.
  4. Type Formula Name and Select Date and return type
  5. Type Following in your formula

    DATEVALUE(text( Year__c) & '-' & text(Month__c) & '-' & '01')

Reference guide to Date formula

  • Error: Field Year__c is a picklist field. Picklist fields are only supported in certain functions. Tell me more
    – Hans
    Nov 19 '15 at 21:32
  • put all picklist value inside TEXT(Year__c) formula.
    – Himanshu
    Nov 19 '15 at 21:33
  • What field typ would work? Text, Number, ...?
    – Hans
    Nov 19 '15 at 21:33
  • make return type as Date
    – Himanshu
    Nov 19 '15 at 21:33
  • Now I get: Error: Incorrect parameter type for operator '-'. Expected Number, Date, DateTime, received Text
    – Hans
    Nov 19 '15 at 21:35

Finally the formula that is working well is:

"DATEVALUE( Year__c & "-" & Month__c & "-" & "01" )"

The field typ of MONTH and YEAR are both (text). The transfering from a picklist value did not work, evan that the Syntax check was positiv.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.