+ Start a Discussion

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.
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.
Hi, thank you for the response, that makes sense, slightly worried about not being able to handle ranges though.  Thanks again.

       CASE( TODAY () -   DATEVALUE (CreatedDate ) ,
             "/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

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