# Conditionally format rows in Excel depending on date range

Sometimes I get asked Excel questions. This is one of those times!

Given a spreadsheet with rows that contain a start and finish date, format the rows in the past, present and future in different colours, so that it looks like this:

I initially tried using a formula with Excel’s Conditional Formatting feature. Despite this being the recommended solution in some search results, I just found it set the one format for all the rows – not what I wanted. This post by Joseph D’Emanuele put me on the right track.

Here’s what I ended up doing:

- Add a new column next to your existing data
- In the first cell of this new column, insert the formula
`=IF(B2 < TODAY(), "Past", IF(A2 > TODAY(), "Future", "Current"))`

- Copy this formula down to the remaining rows. eg.
- Now select all the rows you want to format (for me that’s A2:D5)
- On the
**Home**menu tab, select**Conditional Formatting**, then**Manage Rules** - You will add 3 rules – one for each status.
- Click
**New Rule** - Select
**Use a formula to determine which cells to format** - In
**Format values where this formula is true**, enter**=INDIRECT(“D” &ROW()) = “Future”** - Click
**Format**and choose the desired formatting to apply for Future dates - Click
**OK**and repeat adding new rules for “Current” and “Past” - You should end up with something like this:
- Click
**OK**and you should have rows formatted different colours depending on whether the start/finish date is in the past, current or in the future! - You can optionally choose to hide the Status row if you’d rather not see it all the time.

Note that the formula in the conditional formatting (=INDIRECT(“D” &ROW()) = “Future”) is hard-coded to the column – “D” in my case. If you move data around, you’ll need to update this to refer to the new column letter.

Categories: Excel