Color Status Date
I'm trying to make it so the following guidelines are followed from the "Auto Filled in Date" to the "Date Entire Request is Complete/Sent to Law":
-Green – An open request that was received 1-5 days ago.
-Yellow – An open request that was received 6-10 days ago.
-Red – An open request that was received more than 10 days ago.
-Blue - Complete
-Blank/No Date - Blank status
=IF((ISBLANK([Information Requested -Auto Filled in Date]51), " "), IF((ISDATE([Date Entire Request is Complete/Sent to Law]51), "Blue"), IF(([Information Requested -Auto Filled in Date]51 >= TODAY(), "Yellow"), IF(([Information Requested -Auto Filled in Date]51 >= TODAY(10), "Red"), IF(([Information Requested -Auto Filled in Date]51) >=TODAY(5)"Green")))))
I thought the issue was a lack of closed out arguments, so I added more parentheses. I can't seem to figure it out.
Comments
-
You actually have a lot of unnecessary parenthesis scattered about and a missing comma. I also rearranged the order a little bit to make sure that it wouldn't get hung up on a true value and stop running before it finished out. Give this one a shot...
=IF(ISBLANK([Information Requested -Auto Filled in Date]51), "", IF(ISDATE([Date Entire Request is Complete/Sent to Law]51), "Blue", IF([Information Requested -Auto Filled in Date]51 >= TODAY(10), "Red", IF([Information Requested -Auto Filled in Date]51 >= TODAY(5), "Yellow", IF([Information Requested -Auto Filled in Date]51 >=TODAY(0)"Green")))))
-
Thank you for your reply! I copied and pasted what you gave, and it still came back as "UNPARSEABLE."
-
My apologies. Missed a comma towards the end.
=IF(ISBLANK([Information Requested -Auto Filled in Date]51), "", IF(ISDATE([Date Entire Request is Complete/Sent to Law]51), "Blue", IF([Information Requested -Auto Filled in Date]51 >= TODAY(10), "Red", IF([Information Requested -Auto Filled in Date]51 >= TODAY(5), "Yellow", IF([Information Requested -Auto Filled in Date]51 >=TODAY(0), "Green")))))
-
That one yielded a blank box.
My program manager and I are trying to also see what we can do to figure this out. -
Here is exactly what this formula says...
.
If the Information Requested cell is blank, then blank.
If the Date Entire Request is Completed cell is a date, then Blue.
If the Information Requested Date is 10 or more days in the future, then Red.
If the Information Requested Date is 5 or more days in the future (but less than 10), then Yellow.
If the Information Requested Date is in the future (but less than 5 days), then Green.
Everything else will also be blank.
.
I based this off of your formula in your original post. Further review has shown that it is a matter of adjusting the date criteria as that does not match what you typed in your original post.
.
=IF(ISBLANK([Information Requested -Auto Filled in Date]51), "", IF(ISDATE([Date Entire Request is Complete/Sent to Law]51), "Blue", IF(TODAY() >= [Information Requested -Auto Filled in Date]51 + 10, "Red", IF(TODAY() >= [Information Requested -Auto Filled in Date]51 + 5, "Yellow", IF(TODAY() >= [Information Requested -Auto Filled in Date]51, "Green")))))
.
Here is what this one says...
.
If the Information Requested cell is blank, then blank.
If the Date Entire Request is Completed cell is a date, then Blue.
If the Information Requested Date is 10 or more days in the past, then Red.
If the Information Requested Date is 5 or more days in the past (but less than 10), then Yellow.
If the Information Requested Date is in the past (but less than 5 days), then Green.
Everything else will also be blank.
.
This should provide the results of what you said as opposed to replicating the results of your formula as I had previously done. Give this a try and let me know how it works.
-
It works beautifully! Thank you.
