+ Start a Discussion
Nida Khan 5Nida Khan 5 

Create a formula that shows where an Opportunity is in the pipeline.

Hello All,
I have completed this challenge.

1-For this first you need to create a helper formula field(type-percent) 
Percent Completed :
(DATEVALUE( CreatedDate ) - CloseDate )/100

2- Then you need to create the actual formula field (type- text) by using the helper formula field.
Opportunity Progress :

IF( Percent_Completed__c <=25,"Early", 
IF(Percent_Completed__c <=75,"Middle", 


Joe SchmittJoe Schmitt
This worked for me :)

This seems to be not working for me. Can some one please help me with the below scenario.

Create a formula field that classifies an Opportunity as either “Early”, “Middle”, or “Late”. This formula field should use TODAY() to calculate what percentage of the time between an opportunity’s CreatedDate and CloseDate has passed, and label the opportunity accordingly.This formula should be on the Opportunity object
This formula should be named 'Opportunity Progress' with the resulting API name Opportunity_Progress__c
This formula should return 'Early' if less than or equal to 25% of an opportunity has passed
This formula should return 'Middle' if between 25% and 75% of an opportunity has passed
This formula should return 'Late' if more than 75% of an opportunity has passed
This formula should reference a helper formula field, also on the Opportunity Object, with the type Percent and the name Percent Completed
Percent Completed should return the percentage of the time that has passed between an opportunity’s CreatedDate and CloseDate

Percentage Completed field:
(DATEVALUE( CreatedDate ) - CloseDate ) / 100

Opportunity progress :
IF( Percent_Completed__c < 25 , "Early", 
IF(Percent_Completed__c >25 && Percent_Completed__c <75 ,"Middle","Late"))

On opportunites its showning me wrong values for percentage field.

J CesarJ Cesar
Vamshi is right. Nida passed the challenge because of a loophole in the check. The question asks you to use TODAY() in the formula, that should be a strong hint. You are asked to calculate the percentage of time lapsed from the created date compared to the whole pipeline of the opportunity.

Opportunity pipleline is the time passed betweencreated date and close date. What you are looking for is the time passed between today and created date, then divide that by total lifespan (pipeline) of the opportunity and you'll get the correct percentage. It works and you'll pass the challenge AND get the correct values.

This is the formula to get the time passed in percentage:
(TODAY() - DATEVALUE(CreatedDate))/(CloseDate - DATEVALUE(CreatedDate))
You need to use DATEVALUE because CreatedDate is a Date/Time field.
Michael Kolodner 6Michael Kolodner 6
I've got the time in percentage, but I can't get a formula to check that against the values. I'm trying to do it using CASE (rather than a series of IFs).  But for some reason, it doesn't like the less than or equal sign. I know I'm missing something simple here. Can someone help?
CASE ( Percent_Completed__c, 
 <= .25 , "Early",
J CesarJ Cesar
Hi Michael,

CASE statements expect a fixed value, not a range, that's why your formula doesn't work. CASE is not meant as a replacement for IF statements, their functionalities differ. IF statement evaluates a condition and then executes when the condition is true (boolean), CASE is like a switch board between fixed values with the option of default.
If you insist on using case you would need to convert the 3 ranges to fixed values and then use them in your formula but that is really unnecessary.
karen eich 7karen eich 7
New related question, i thought i had the challenge completed until it errored out at the end and indicated that the opportunity progress field is supposed to be a number field. that seems odd to me when we are really returning text items, right (late, middle, early?) or am i just tired and confused? 
karen eich 7karen eich 7
never mind. i figured it out.
Nida Khan 5Nida Khan 5
Hi Karen,

Opportunity progress field should be formula field with return type "text" as we are returning text values. Hope, you have figured out this.
Jagannath Birajdar 10Jagannath Birajdar 10
Opportunity Progress formula should be:
IF( Percent_Completed__c <=0.25,"Early", 
IF(Percent_Completed__c >0.25 && Percent_Completed__c <0.75,"Middle", 
IF(Percent_Completed__c >0.75,"Late",NULL) 
ryan woods 8ryan woods 8
Tried everything still getting error's on both parts! Thanks
ryan woods 8ryan woods 8
I used this even though I got errors I saved it and checked challenge word seamlessly. 
(DATEVALUE( CreatedDate ) - CloseDate )/100
Rajeshwar PatelRajeshwar Patel
Hi Alll,
           i m getting this error . But my task was complited sucessufully . I checked it..But i m getting this error . So only i m not able to check in trilhead..

Challenge Not yet complete... here's what's wrong: 
There was an unexpected error in your org which is preventing this assessment check from completing: System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Discount_Percent__c]: [Discount_Percent__c]

Can any one please asnwer it ?

Hello Rajeshwar, 
This error is not related to the current challenge.
I think that you have created a custom field and a trigger on the same org, in order to complete another challenge, maybe the Bulk Apex Triggers Trailhead (https://developer.salesforce.com/forums/?id=906F0000000BU1ZIAW). From the error message, I would say that your trigger is not working because of a universally required field (https://help.salesforce.com/HTViewHelpDoc?id=fields_about_universally_required_fields.htm&language=en_US) called "Discount_Percent__c". A universally required field is a custom field that must have a value whenever a record is saved within Salesforce, the Force.com API, automated processes etc. So, when that field is empty on a record, you get an error. You should remove this option from the field. Go to : Customize \ <Object where the field is located, maybe "Opportunities"> \ Fields \ Edit the "Discount Percent" field \ Untick the checkbox called "Required" (located in the block called "General Options") \ Save.
Leo TorresLeo Torres
@Jagannath thanks, I kept leaving out NULL at the end. I knew I had to address the false state somehow but I guess I assumed my option would be in the formula list.
Hi everybody,

Anyone can explain me, the reason that we don't use the TODAY() for this excercice. 
In which way can we use it or Not?
Salvatore EspositoSalvatore Esposito
@Jagannath Birajdar 10
Your formula is wrong! If you have Percent_Completed exactly at 0.75 you will have a wrong result.

@Saeed CHIADMI It's impossible to avoid using of Today(). How can i calculate the actual pipeline without Today()?
Krishna RaviKrishna Ravi
I used the below formula for "Percent Completed" and passed the challenge
( TODAY()  -  DATEVALUE( CreatedDate )) / (CloseDate - DATEVALUE( CreatedDate ))
Mayank shahMayank shah
Percent completed:
(TODAY() -DATEVALUE(CreatedDate))/ (CloseDate-DATEVALUE(CreatedDate))

Opportunity Progress:
 Percent_Completed__c <=0.25,'Early',
   if(Percent_Completed__c > 0.25 || Percent_Completed__c <= 0.75,'Middle', 'Late'
Maria HuemmerMaria Huemmer
With formulas that return text, it is a good idea to write them exclusively so that the first criteria that is met is the value returned. Here's a way to calculate Opportunity Process once Percent Completed is created:

IF((Percent_Completed__c)>0.25, 'Middle', 

sachin katolkar 13sachin katolkar 13
Hi All ,

I am getting below error for the this challenge

Challenge Not yet complete... here's what's wrong: 
The 'Opportunity_Progress__c' formula field does not exist or is not of type 'Number'

I chose the Opportunity Progress as a TEXT as the value is TEXT (Early, Middle,Late) and not allowing Number data type.

Please help me on this.

Krystian OdrobinaKrystian Odrobina
I've tested below and can confirm that it works.

Percent Completed > Formula (Percent)
(TODAY() - DATEVALUE(CreatedDate))/(CloseDate - DATEVALUE(CreatedDate))

Opportunity Progress > Formula (Text)
IF( Percent_Completed__c <= 25 , "Early", 
IF(Percent_Completed__c >25 && Percent_Completed__c <=75 ,"Middle","Late"))
Andrei MilitaruAndrei Militaru
This is absolutely ridiculous, I'm getting this message:

User-added image

type is obviously text, since you want to return "Early"...
Percent Completed > Formula (Percent)

Opportunity Progress > Formula (Text)
IF(Percent_Completed__c  <=  0.25, "Early", 
   IF(Percent_Completed__c   >=   0.75, "Late", "Middle") )
Pedro RodriguesPedro Rodrigues
Percent Completed > Formula (Percent)
(TODAY() - DATEVALUE(CreatedDate))/(CloseDate - DATEVALUE(CreatedDate))

Opportunity Progresss > Formula (Text)
IF (Percent_Completed__c <= 0.25, "Early",
  IF (Percent_Completed__c > 0.25 && Percent_Completed__c < 0.75, "Middle",
Durgamba RayuduDurgamba Rayudu
Hi i used the formula to create Percent Completed of type percent

( TODAY()  -  DATEVALUE( CreatedDate )) / (CloseDate - DATEVALUE( CreatedDate ))
but getting syntax error Field CreatedDate may not be used in this type of formula
Please suggest me. i am not able to crete this filed itself.
Rajesh BairyRajesh Bairy
1. (TODAY() - DATEVALUE(CreatedDate))/(CloseDate - DATEVALUE(CreatedDate))
2. IF (Percent_Completed__c <= 0.25, "Early",
      IF (Percent_Completed__c > 0.25 && Percent_Completed__c < 0.75, "Middle",

Used aboe formula but i got this Error:

There was an unexpected error in your org which is preventing this assessment check from completing: System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Discount_Percent__c]: [Discount_Percent__c]
My Trailhead Playground 3
Check challenge to earn 500 points
Pasumarthy RaghavendraPasumarthy Raghavendra
Thanks nida, Your solutions perfectly worked for me.
Barbara NilaBarbara Nila
Hi all,
I've come across the same issue Durgamba ran into, for some reason while trying to use (TODAY() -DATEVALUE(CreatedDate))/ (CloseDate-DATEVALUE(CreatedDate)) I receive the same syntax error Field CreatedDate may not be used in this type of formula. Is anyone able to give me any insight to this problem? I feel like I've tried everything to fix it, I even went as far as to try using a new org entirely. 
Devon PlopperDevon Plopper
Barbara, I am also experiencing this same issue and am not able to come up with any answers either? Anybody have any insight on the syntax error regarding not being able to use the "CreatedDate" in the formula? 
Eric Hillman 8Eric Hillman 8
Barbara and Devon - The field type is formula, not percent - the formula return type is percent.  I was fighting that for about 20 minutes before I figured that one out.

i have writen the formuale in validation rules of oppournity 

and it is showing me an error message as
Error: Formula result is data type (Number), incompatible with expected data type (true or false).
i have my data type as a percentage 
stil it is showing the same errror

can anyone pease figure it out ??

Neil CummingsNeil Cummings
Academic Master is a US-based writing company that provides thousands of free essays to the students all over the World
Deepak KalaDeepak Kala

So, here is the complete solution:
1- Create a formula with API name Percent_Completed__c and data type as Percent.
2- The formula will be

(TODAY ()- 
(CloseDate - 

3- Create another formula with API name Opportunity_Progress__c and data type as text
4- The formula will be
IF (
      Percent_Completed__c<= 0.25, "Early",
IF (
      Percent_Completed__c>0.25 && Percent_Completed__c<=0.75, "Middle",
IF (
      Percent_Completed__c> 0.75, "Late",

I hope it helps.


Deepak Kala