Conditional Formatting Based On Time

[Hedva]

I would like to set some cells in a column that contain a time to change their font and backgroud color when the cell doesn’t contain a specific time.

I tried in conditional formating ‘Formula is’:

IF(INDIRECT(ADDRESS(ROW(), COLUMN()))<TIME(14;30;0))

But it doesn’t seem to work. How can I achieve this?

[fcosta]

Apparently this question should be closed, right?

[Lupp]

1. A Conditional Format (CF) is always applied to the cells it is defined for. To reference “ThisCell” you don’t need INDIRECT() and ADDRESS() or OFFSET(), but just the relative reference to the leftmost topmost cell of the respective range.
2. Your parameter delimiters are inconsistent: First you used a comma, later-on semicolons. The semicolon should be accepted by any locale (probably exchanged then automatically for a comma).
   This being the reason for your failure, you should have gotten Err:508 for wrong / unpaired parentheses. Please always report error messages.
3. As your IF() expression does neither contain an if-part nor an else-part, I assume you wanted it to return TRUE or FALSE repectively to then control the CellStyle to overlay. If so you better used simply
   INDIRECT(ADDRESS(ROW(); COLUMN()))<TIME(14;30;0) already returning the same logical result.
   Assuming your CF range being, say, B7:C15, regarding my first point, the simplified condition to use in the CF manager would be B7<TIME(14;30;0).

[Hedva]

Thanks. I’m pretty new to this. I almost never use programs like Calc (or Excel) so I didn’t knew about that.
When I do, I don’t have to use custom formulas because almost everything is already provided as default options in the menu’s. Thanks for helping me out

[Lupp]

You’re welcome!