function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
KazjamKazjam 

Issue Status using today and created date help required

Hi, I have a scenario where I want to create a status of red, yellow green using
Today - created date so if the value of difference is between 0-2.99 then green, if
3-6.99 then yellow, 7+ then red.   I thought the following would do this:
 
IMAGE( CASE( TODAY () -   CreatedDate, "0-2.99", "/img/samples/light_green.gif", "3-6.99", "/img/samples/light_yellow.gif",  "7+", "/img/samples/light_red.gif", "/s.gif"), "status color")
 
However I am getting the following:
 
Error: Incorrect parameter for function -(). Expected Number, Date, DateTime, received TextError: Incorrect parameter for function -(). Expected Number, Date, DateTime, received Text
 
As I am clearly missing something yet again :>( please could someone point me on the right path as this formula stuff is proving tricky.   Thank you advance.
 
 
AdminisaurusRexAdminisaurusRex
Odd thing, I am no longer to replicate this error message.  When I first started testing your formula, I was able to get it.  Now I can't get that error message back.
 
Make sure that the formula type is text.
 
Also, I don't think that you can use ranges as result criteria.  The formula as you have written it will perform the calculation and then look for the text "3-6.99", etc.
 
 
KazjamKazjam
Hi, thank you for the response, that makes sense, slightly worried about not being able to handle ranges though.  Thanks again.
AdminisaurusRexAdminisaurusRex

IMAGE(
       CASE( TODAY () -   DATEVALUE (CreatedDate ) ,
             1,"/img/samples/light_green.gif",
             2,"/img/samples/light_green.gif",
             3,"/img/samples/light_green.gif",
             4,"/img/samples/light_yellow.gif",
             5,"/img/samples/light_yellow.gif",
             6,"/img/samples/light_yellow.gif",
             7,"/img/samples/light_red.gif",
             8,"/img/samples/light_red.gif",
             9,"/img/samples/light_red.gif",
             10,"/img/samples/light_red.gif",
             "/s.gif" ),
        "Status Color" )

I found the solution to your "Error: Incorrect parameter for function -(). Expected Number, Date, received DateTime " problem.  The TODAY function returns a date-type data, but CreatedDate is datetime-type data, and you can't calculate anything that is not the same data-type.  So use the DATEVALUE formula to turn CreatedDate into date-type data.

It would be useful for CASE to use ranges, but until it does, I have posted the long-hand version.  The formula above should work for you.  I am going to post an Idea suggesting that CASE be allowed to use ranges.



Message Edited by AdminisaurusRex on 11-25-2008 06:47 AM
KazjamKazjam

Hi, Wow that's fantastic, thank you so much. I'll vote for the idea too.

:smileyhappy: