+ Start a Discussion
Bj LarssonBj Larsson 

Trailhead - Advanced Forumulas: Calculate percentage of opportunity time remaining

Hi folks,

I struggling to understand why the following returns an error:
(DATEVALUE (CloseDate) - TODAY ()) / (DATEVALUE (CloseDate) - DATEVALUE (CreatedDate))

I'm trying to get the percentage of time remaining in the opportunity, part of this trail:
https://developer.salesforce.com/trailhead/force_com_admin_advanced/advanced_formulas/implementing_advanced_formulas 

The error I get is: Error: Incorrect argument type for function 'DATEVALUE()'. 

When I review the Advanced - date / time module, it give an example for how to calculate the number days using:
TODAY() - DATEVALUE(CreatedDate), and I'm stuck trying to figure out where I went wrong.

Any help would be greatly appreciated.

Thanks

Bjorn

 
Best Answer chosen by Bj Larsson
SandhyaSandhya (Salesforce Developers) 
Hi Bj Larsson,

Percent Completed should return the percentage of the time that has passed between an opportunity’s CreatedDate and CloseDate

 So the formula basically needs to calculate the ratio of the difference between Today's date and Created Date AND the total time between the Close Date and Created Date. And when you store that in a Percentage field, the ratio automatically gets displayed as a Percentage. So the numerator and denominator in your formula will have to numbers instead of dates since a ratio of dates doesn't make sense, right?


Percent Completed field formula:
(TODAY() -DATEVALUE(CreatedDate))/ (CloseDate-DATEVALUE(CreatedDate))

Opportunity Progress field formula:
IF( 
   Percent_Completed__c >= 0.75, "Late", 
        IF ( 
              Percent_Completed__c >= 0.25, "Middle", 
              "Early" 
   ) 
)

Hope this helps you!

Please accept my solution as Best Answer if my reply was helpful. It will make it available for other as the proper solution. If you felt I went above and beyond, you can give me kudos.
 
Thanks and Regards
Sandhya

 

 

All Answers

FearNoneFearNone
I'm guessing that your CloseDate and/or CreatedDate are date and not date/time.
you can change their datatype into date/time or convert them to datetime.

DATEVALUE  only accepts datetime value.
so, something like this...
Datetime dtClose = datetime.newInstance(CloseDate.year(), CloseDate.month(),CloseDate.day());
Datetime dtCreate = datetime.newInstance(CreatedDate.year(), CreatedDate.month(),CreatedDate.day());
(DATEVALUE (dtClose) - TODAY ()) / (DATEVALUE (dtClose) - DATEVALUE (dtCreate))

 
SandhyaSandhya (Salesforce Developers) 
Hi Bj Larsson,

Percent Completed should return the percentage of the time that has passed between an opportunity’s CreatedDate and CloseDate

 So the formula basically needs to calculate the ratio of the difference between Today's date and Created Date AND the total time between the Close Date and Created Date. And when you store that in a Percentage field, the ratio automatically gets displayed as a Percentage. So the numerator and denominator in your formula will have to numbers instead of dates since a ratio of dates doesn't make sense, right?


Percent Completed field formula:
(TODAY() -DATEVALUE(CreatedDate))/ (CloseDate-DATEVALUE(CreatedDate))

Opportunity Progress field formula:
IF( 
   Percent_Completed__c >= 0.75, "Late", 
        IF ( 
              Percent_Completed__c >= 0.25, "Middle", 
              "Early" 
   ) 
)

Hope this helps you!

Please accept my solution as Best Answer if my reply was helpful. It will make it available for other as the proper solution. If you felt I went above and beyond, you can give me kudos.
 
Thanks and Regards
Sandhya

 

 
This was selected as the best answer
Bj LarssonBj Larsson
Thank you Sandhya, that explanation makes sense.  I'm not quite clear yet what returns a date versus a number, but will keep reading to make sure I understand.

Bjorn