Creating a Formulla

Vinton Douglas

Dear Team,

I am in need of assistance to write a formula to entail 3 columns, namely

1. Read status to display (bubble symbols)-RAG
2. Date Policy Read
3. Retirement Date of Policy

The following conditions are required:

Any help would be grateful and thanks in advance.

Kelly Moore

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.

https://help.smartsheet.com/function/netdays

Kelly

Kelly Moore

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

Kelly Moore

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.

https://help.smartsheet.com/function/netdays

Kelly

Vinton Douglas

Hi Kelly,

Much appreciated. This works flawlessly.

Vinton Douglas

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.

Kelly Moore

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

Vinton Douglas

Much appreciated.