Color Status Date

fennerb

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.

Paul Newcome

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")))))

fennerb

Thank you for your reply! I copied and pasted what you gave, and it still came back as "UNPARSEABLE." 

Paul Newcome

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")))))

fennerb

That one yielded a blank box. My program manager and I are trying to also see what we can do to figure this out.

Paul Newcome

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.

fennerb

It works beautifully! Thank you. 

Paul Newcome

Excellent! Happy to help!