Hi there,New to Quickbase, but have a big MS Excel background. What I used to find easy in Excel, I can't seem to get my head around in QB. If anyone can help enlighten my rookie brain, it would be greatly appreciated.I'm trying to colour-code a date type field based on multiple conditions:Say for example I have field called 'Survey Required By', which is the date for when we want a survey to be completed, and then another field called 'Survey Completed', and in this field we enter the date when the survey is completed.I want to highlight the field text or background in red if the 'Survey Required By' date is today or older, and the 'Survey Completed' field is blank.I've googled and tired many different formulas, but the best I've managed is to color code a whole row on a report, which I don't want to do. I just want the 1 field color changed.The formula I have so far is:if( isnull([Survey Completed]) and [survey required by]=today(), "red","" )

What is the name of your Rich Text field? Try adding it to the report. :)

Color Code Date Field based on Formula

MarkShnier__You
Qrew Legend
5 months ago
You will need to create a new Formula Rich Text field. It's not like Excel where you can format the cell
here are some examples:

"<div style=\"color:black;background-color:red;\"><div>" & [Answer]

or for a simple bold red
"<font color=red><b>" & [my text field]
- bottyz
  Qrew Member
  5 months ago
  hi mark,
  Thanks for the reply.
  If I was to change it to a formula rich text field, would that mean it won't naturally format into a date? i.e. appear on the record as 04-JUN-2024?
  And will the user see the formatting code? And therefore be able to accidentally change it?
  - MarkShnier__You
    Qrew Legend
    5 months ago
    Perhaps I didn't communicate clearly enough. Your date field will remain a date field. In addition to the date field, you will set up a brand new formula Rich Text field to present that date field on various forms or report as a Rich Text field.
    
    like this
    "<font color=red><b>" & ToText([my date field])
MarkShnier__You
Qrew Legend
5 months ago
In order to check whether a text field is blank you need to check it against empty quotes. It is obscure, but it is documented that The is no function. Does not work on text fields.

In order to check whether a text field is blank you need to check it against empty quotes. It is obscure, but it is documented that The is no function does not work on text fields.

So if [survey completed] as a text, feel like yes no,

then use

if([Survey completed] = ""

another tip is that if you were checking a freeform data entry text field to be blank. I do this.

if (Trim([my field]) = "", ......
Just in case the user has entered some empty spaces in the field
- bottyz
  Qrew Member
  5 months ago
  Hi Mark,
  Ok I've tried that but get another error as the [Survey Completed] field is a date format field, ="" doesn't then work.
  - MarkShnier__You
    Qrew Legend
    5 months ago
    Ok, so both fields are date fields. Then IsNull should work fine.
    
    i would think this should work.
    
    If( IsNull([Survey Completed]) and [Survey Required By]<=Today(), "<div style=\"color:white;background-color:red;\">" & ToText([Survey Required By]) & "<div>" ,
    ToText([Survey Required By])
MarkShnier__You
Qrew Legend
5 months ago
What is the name of your Rich Text field? Try adding it to the report. :)
- bottyz
  Qrew Member
  5 months ago
  Ah ha! I understand now... this new field replaces the original in the report!
  Thanks for pointing that out!
  - MarkShnier__You
    Qrew Legend
    5 months ago
    It's hard to unlearn Excel. I used to be pretty good at Excel, but now if I have to do a complicated formula in Excel I have to unlearn Quickbase.
    This video says it all

Forum Discussion

Color Code Date Field based on Formula