Ask Your Question

Conditional Formatting Based On Time

asked 2017-04-13 15:08:03 +0200

Hedva gravatar image

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':


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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-04-13 15:53:06 +0200

Lupp gravatar image

updated 2017-04-13 16:16:36 +0200

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).

edit flag offensive delete link more


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 :)

Hedva gravatar imageHedva ( 2017-04-14 13:49:04 +0200 )edit

You're welcome!

Lupp gravatar imageLupp ( 2017-04-14 14:10:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-13 15:08:03 +0200

Seen: 397 times

Last updated: Apr 13 '17