Problem with formula for setting color in Textbox

[#1]

Hello everybody,

I have a problem with a formula. The formula looks like:

IF(index(concat(Datum_verg&Severity,','),'4NM')>0,Yellow(),

IF(index(concat(Datum_verg&Severity,','),'4I')>0,LightBlue(),

IF(index(concat(Datum_verg&Severity,','),'4A')>0,LightRed(),

IF(DAY(MakeDate(Year(Today()),Month(Today()),4))<DAY(Today()),LightGreen(),White()))))

For examplI if I have data in my table for the 24.01.2014 so Datum_verg will create with 24. The textbox of the 24 will mark with the color for Severity. But the Textbox for the 4 will also mark like the 24.

See for more information the attachment.

What can I do can anyone help me.

Thanks

grettings sven

[giakoum]

Try using subfield instead of index

[#3]

Can you please explaine or insert an example thanks

[giakoum]

so it does, because the search string is present in both string (4NM and 24NM both contain 4NM). Is there no other field  to use to determine?

[jerem1234 · Accepted solution]

So your problem is with the formula marking both 4NM and 24NM with the same value? Why don't you create dummy characters in the beginning, middle and end like:

=IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|NM|')>0,Yellow(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|I|')>0,LightBlue(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|A|')>0,LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

or maybe even better, use match function such as:

=IF(max(match(Datum_verg&Severity,'24NM')),Yellow(),

  IF(max(match(Datum_verg&Severity,'24I')),LightBlue(),

  IF(max(match(Datum_verg&Severity,'24A')),LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

If this doesn't help, can you explain a little more of what you are trying to achieve?

Hope this helps!

[#6]

Hi jerem,

I use the second function and it works. Thanks for your help.

I use this for a safteycross for our plant, so we have a table for all accidents in it. The colors ar standing for different severitys. So we can represent the achtually month on our teamboards

Greetings Sven