Status Field - Conditional Formatting or If Function?

Lee Ratzlaff

I am working on creating a tracking sheet for a longitudinal study, where clients need to be re-interviewed every six months until they are discharged from the program. I have a status field for each time point, and have used conditional formatting to change the color of the cell for each record, depending on the situation, but I'm wondering if it makes more sense to write in If statements and use a symbol field instead.

I have the conditional formatting structured as such:

• If the 6m Interview (NOMs) field is a date, FORMAT status cell green
• If the 6m Interview field is blank AND the 6m interview window (calculated date) field is in the next 30 days AND the Discharge Date field is blank, FORMAT status cell yellow
• If the 6m interview field is blank AND the 6m interview window field is in the next 15 days AND the Discharge Date field is blank, FORMAT status cell bright red
• If the 6m interview field is blank AND the 6m interview window field is in the past AND the Discharge Date field is blank, FORMAT status cell dark red

I will repeat this process for 12m, 18m and 24m time points.

Will conditional formatting show up in reports and/or dashboards? Or is this why using a symbol field a better choice? Screen shot of sheet attached for context.

Thanks!!

Andrew Stewart

Hi Lee,

Conditional formatting will show up in reports and dashboards.

Reasons for using a calculation in a symbol field (or other) include:

1) You can't sort and filter on conditional formatting, so if you wanted to create a report of just red and dark red, you would need a calculated field or to reproduce the conditional formatting rule in the filter, which adds to maintenance

2) Conditional formatting is quite flexible, but still has limitations (for instance if you wanted to have 15 working days instead of 15 days)

Reasons for using conditional formatting over a formula in another column include:

1) You can highlight the field that is going to need updating, for instance you could highlight the 6 month interview field itself if it is blank and needs to be set. Much easier than just seeing the status and having to go update a different column, especially if there are lots!

2) You have a lot more variety of options that the symbol sets provided

3) You use up one less column, which can be important if you are using very wide sheets

I often combine both approaches, so I have a complex formula in a hidden column with a simple rule for highlighting the specific cell I want. This also helps if you want more categories than the symbols provided.

Regards,

Andrew

Andrew Stewart

Hi Lee,

Conditional formatting will show up in reports and dashboards.

Reasons for using a calculation in a symbol field (or other) include:

1) You can't sort and filter on conditional formatting, so if you wanted to create a report of just red and dark red, you would need a calculated field or to reproduce the conditional formatting rule in the filter, which adds to maintenance

2) Conditional formatting is quite flexible, but still has limitations (for instance if you wanted to have 15 working days instead of 15 days)

Reasons for using conditional formatting over a formula in another column include:

1) You can highlight the field that is going to need updating, for instance you could highlight the 6 month interview field itself if it is blank and needs to be set. Much easier than just seeing the status and having to go update a different column, especially if there are lots!

2) You have a lot more variety of options that the symbol sets provided

3) You use up one less column, which can be important if you are using very wide sheets

I often combine both approaches, so I have a complex formula in a hidden column with a simple rule for highlighting the specific cell I want. This also helps if you want more categories than the symbols provided.

Regards,

Andrew