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:

1. Add a new column next to your existing data
2. In the first cell of this new column, insert the formula =IF(B2 < TODAY(), "Past", IF(A2 > TODAY(), "Future", "Current"))
3. Copy this formula down to the remaining rows. eg.
4. Now select all the rows you want to format (for me that’s A2:D5)
5. On the Home menu tab, select Conditional Formatting, then Manage Rules
6. You will add 3 rules – one for each status.
7. Click New Rule
8. Select Use a formula to determine which cells to format
9. In Format values where this formula is true, enter =INDIRECT(“D” &ROW()) = “Future”
10. Click Format and choose the desired formatting to apply for Future dates
11. Click OK and repeat adding new rules for “Current” and “Past”
12. You should end up with something like this:
13. 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!
14. 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.

On the NBN

We switched over to the NBN at home a couple of months ago. Unfortunately since the last change of Government in Australia, it appears that all NBN rollouts are getting fibre to the node (FTTN) rather than original plan of fibre to the premises (FTTP). So with the node servicing our neighbourhood being a fair distance from our house, it looks like the best we can expect for the foreseeable future is ~40MB. That is a 4x increase on what we used to get with ADSL2+, but it is a shame we can’t get the full 100MB if we wanted to pay for it.

40MB isn’t too bad – I know of others that are only getting 20 (and there’s stories of some that switch over from ADSL to get a slower speed than what they used to have). Just seems a lost opportunity that if everyone had got fibre, I’m sure that would be capable of being upgraded in the future to even beyond 100MB.

ShaggyMax screen protectors

My old laptop ended up getting a really scratched display. The keyboard obviously had been rubbing against the glass for a while, which was a shame. I think this was made worse because the only thing separating them was two rubber feet that had come loose after a few years.

My new laptop has a different design with a small rubber strip that goes all the way around the edge, which seems to do a better job. Nevertheless, I did want to get something that would help protect the display and reduce the chance of scratches.

A bit of Googling came up with a few options. A call out to Twitter ended up with an in-person response from Ryan showing me he’d got a ShaggyMax-brand protector for his brand new 9560 (the newer version of my 9550).

That was good enough for me, so I’ve now got my own!

I bought a 15.4” Laptop Screen Protector. You can buy them through Amazon, or direct from their website. Here’s what it looks like on my laptop:

It’s a microfibre cloth that is thin enough that it doesn’t interfere with closing the lid, but hopefully will be thick enough to softly cushion and protect the glass. So far, so good.