Creating a Formulla
Dear Team,
I am in need of assistance to write a formula to entail 3 columns, namely
- Read status to display (bubble symbols)-RAG
- Date Policy Read
- Retirement Date of Policy
The following conditions are required:
Any help would be grateful and thanks in advance.
Best Answers
-
Hey @Vinton Douglas
Try this
=IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")))
Because functions that use dates, like NetDays, produce errors when it can't find a date, I added a check to make sure both [Date Policy Read] and [Retirement Date of Policy] had dates in the cells. If no date(s) are found then the [Read Status] field will be blank. If you wish a color to be displayed instead your formula would be this:
=IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")), "Whatever Color")
Here is information on the NetDays function I used.
Kelly
-
Yes, I think so. To be clear, you would have to choose a 4 color symbol format (the 4th color is either gray or blue, depending on your selection). If I'm understanding correctly, if, let's call it the 'Title', ie probably your primary column, is not populated, the Date fields would not be populated either. The formula below is for this assumption, we can tweak if that isn't right. I'm also choosing the Gray color balls - you can adjust if Blue. [You could also choose Red, Yellow or Green if desired]
=IF([Title Column]@row = "", "", IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")), "Gray"))
Answers
-
Hey @Vinton Douglas
Try this
=IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")))
Because functions that use dates, like NetDays, produce errors when it can't find a date, I added a check to make sure both [Date Policy Read] and [Retirement Date of Policy] had dates in the cells. If no date(s) are found then the [Read Status] field will be blank. If you wish a color to be displayed instead your formula would be this:
=IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")), "Whatever Color")
Here is information on the NetDays function I used.
Kelly
-
Hi Kelly,
Much appreciated. This works flawlessly.
-
Hi Kelly,
I love the idea with the second formula to create 'any color' if a policy is present but not populated in the 'Date Policy Read' column. The issue however, is that the 'any color' remains in place even after removing the name of the policy. Can this be corrected so that if the area is unpopulated, the 'any color' goes?
Thanks again.
-
Yes, I think so. To be clear, you would have to choose a 4 color symbol format (the 4th color is either gray or blue, depending on your selection). If I'm understanding correctly, if, let's call it the 'Title', ie probably your primary column, is not populated, the Date fields would not be populated either. The formula below is for this assumption, we can tweak if that isn't right. I'm also choosing the Gray color balls - you can adjust if Blue. [You could also choose Red, Yellow or Green if desired]
=IF([Title Column]@row = "", "", IF(AND(ISDATE([Date Policy Read]@row), ISDATE([Retirement Date of Policy]@row)), IF([Date Policy Read]@row >= [Retirement Date of Policy]@row, "Red", IF(NETDAYS([Date Policy Read]@row, [Retirement Date of Policy]@row) <= 90, "Yellow", "Green")), "Gray"))
-
Much appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!