Highlighting two fields in a report

Try this:

If (


[OWN CALL BACK], "blue",

[Next call back due on]<Now(), "red",

[Next call back due on]>Now(), "green",

"yellow")

This will check for the [OWN CALL BACK] first - and show blue if it's checked (no matter what [Next call back due on] is).

Note that if [Next call back due on] is a Date/Time field - you'll almost never get the "yellow" state - since it'll only be "yellow" for a minute before it becomes "red".

If [Next call back due on] is a Date field, then you'd want to change the references to Now() to ToDate(Now()), as follows:

If (

[OWN CALL BACK], "blue",

[Next call back due on]<ToDate(Now()), "red",

[Next call back due on]>ToDate(Now()), "green",

"yellow")

Thank you! This did work!

Would anyone know how I would make the field go RED only if the Next Call Back date is overdue. Currently the field goes Red on the day it is due. Thanks

Can you paste your formula in here?  In the formula I gave below, it's red if [Next call back due on] is past, but yellow if today is the day of [Next call back due on].

If ([Next callback date/time (GMT)]<Now(), "red", [Next callback date/time (GMT)]>Now(), "green", "yellow") - this is what I am currently using and what I am hoping it does is, RED if the next call back is overdue i.e. Not on the day it is due but the day after, Yellow if no 'next call back' date has been entered, and Green if it is up to date i.e. Next call back date hasn't arrived. Hope this makes sense.

Ok - see below for updated formula for date/times.

If the field is a date/time - use this formula:

If (ToDate([Next callback date/time (GMT)]) < ToDate(Now()), "red", ToDate([Next callback date/time (GMT)])> ToDate(Now()), "green", "yellow")


Here - both [Next callback date/time (GMT)] and the current date/time ("Now()") have been converted to a date with the "ToDate" function. Then it'll show "yellow" on the day it's due, and "red" if it's over due (i.e. the next day).

Keep in mind that "Now()" (and functions like "Today()" ) gives you the LOCAL date/time. So if your [Next callback date/time (GMT)] is entered using GMT time - just check to make sure it's doing the right thing when you have different time zones, etc.

I need this same formula almost, I need to know if Highlight {Construction Start Date} in RED if it is before {Anticipated Delivery Date and/or Highlight Yellow if the dates are the same