Search code examples
excelexcel-formulaformattingconditional-statementsformula

Excel; Conditional formatting Date/Time between two columns

I have two columns, for example Column A and B with the below style of set up.

What I am wanting to do is to highlight both columns in a colour based on certain parameters.

For example: If Column B is due to occur AFTER Column A - no colour. If Column B is due to occur at the SAME TIME as Column A - Highlight both cells in yellow. If Column B is due to occur BEFORE Column A - Highlight in Red.

Please see below examples of the kind of data I would be working with, and then below it an example of what it would look like should the conditional formatting colour it in correctly.

Thank you.

Base Data Completed Formatting

Solution

  • OK, figured out that 1800LT = 1800 hours, Local Time(?)

    You will need to create two conditional formatting rules. One for each of your conditions.

    Conditional formatting > Manage Rules... > New Rule... > Use a formula to determine which cells to format.

    Rule 1, set color to Red:

    =IF($B1<$A1,TRUE,FALSE)

    Rule 2, set color to Yellow:

    =IF($A1=$B1,TRUE,FALSE)