Visual Status Alert

jgneely72151

Hello All!

I hope this is an easy soulution. Can anyone assist me with creating at status alert based off of today's date and a start date? I want the Delay Status to automatically put the status listed and color the cell. If a Cleard to Start has a check mark, then it shows no delays and color coded green. At some point, we want to see all green.

Does this makes sense and if so, your assistance is greatly appreciated!

Brian Wilson DC

@jgneely72151 Good evening. This will require two separate steps to achieve. You will have to create a column formula that places the text into the cell based upon the date value. Then you will have to create a series of conditional formatting rules to enforce the highlighting. Your "week ranges" are an odd choice and will result in gaps if you choose that approach. I've added 2 ranges to make the data work. Feel free to modify them as you wish, but if you stick with 1 week after, 2 weeks after and 4 weeks after you are going to have no highlighting or notations between days 1 and 6 being late, and your 2 weeks after range is going to really be for the full 2 and three weeks.

The formula that I used in the delayed status column reads as follows:

=IF([Cleared To Start]@row = true, "No Delay", IF(TODAY(-28) >= [Start Date]@row, "Four Weeks Delayed", IF(TODAY(-14) >= [Start Date]@row, "Two Weeks or More Delayed", IF(TODAY(-7) >= [Start Date]@row, "More Than One Week Delay", IF(TODAY(-1) >= [Start Date]@row, "One Week or Less Delayed", IF(TODAY(7) >= [Start Date]@row, "One Week Prior Or Less", IF(TODAY(14) >= [Start Date]@row, "Two Weeks Prior Or Less", "")))))))

The second step after entering the formula is the create highlighting rules based upon the text that appears in the Delay Status column. See below for example:

Please let me know if you need any additional help.

-Brian

Brian Wilson DC

@jgneely72151 Good evening. This will require two separate steps to achieve. You will have to create a column formula that places the text into the cell based upon the date value. Then you will have to create a series of conditional formatting rules to enforce the highlighting. Your "week ranges" are an odd choice and will result in gaps if you choose that approach. I've added 2 ranges to make the data work. Feel free to modify them as you wish, but if you stick with 1 week after, 2 weeks after and 4 weeks after you are going to have no highlighting or notations between days 1 and 6 being late, and your 2 weeks after range is going to really be for the full 2 and three weeks.

The formula that I used in the delayed status column reads as follows:

=IF([Cleared To Start]@row = true, "No Delay", IF(TODAY(-28) >= [Start Date]@row, "Four Weeks Delayed", IF(TODAY(-14) >= [Start Date]@row, "Two Weeks or More Delayed", IF(TODAY(-7) >= [Start Date]@row, "More Than One Week Delay", IF(TODAY(-1) >= [Start Date]@row, "One Week or Less Delayed", IF(TODAY(7) >= [Start Date]@row, "One Week Prior Or Less", IF(TODAY(14) >= [Start Date]@row, "Two Weeks Prior Or Less", "")))))))

The second step after entering the formula is the create highlighting rules based upon the text that appears in the Delay Status column. See below for example:

Please let me know if you need any additional help.

-Brian

jgneely72151

Thanks so much Brian! I had this feeling there would be gaps with the timeframes given to me. I just want to be sure…does this same formula apply to past and future start dates? I have to go back and as far as November 2024. Again, thank you so much for working this out for me.

Brian Wilson DC

@jgneely72151 Good evening! Yes, this should work for past and future dates. If your date is in the past and you haven't checked off "Cleared to Start" everything over four weeks late will show four weeks late. That's the downside to your timeframes. Which is why I changed the language slightly. Anything that has a start date in the future. That is over two weeks should just appear blank. We can add to the formula if you'd like to have it say something such as, "This start date is more than two weeks in the future."

jgneely72151

One more thing…since this will cover many records, especially records older than 4 weeks or records that would be future dates not in jeapordy of the delay criteria, how would I add to formular? For instance dates March through December will be among this list

jgneely72151

Never mind Brian. Figured out how to add it. It works perfectly. Again, thanks!