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.

Find more posts tagged with

Comments

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.

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…