Conditional formatting for deadline management in Excel

September 8, 2024 9:30 AM

I would like to use conditional formatting in Excel to help me manage a large matrix of deadlines. I want to use colors to indicate which deadlines are approaching and which deadlines have passed. Difficulty level: how to handle tasks that have been completed.

The matrix will look something like this:

Client    Draft Proposal     Send Proposal      Ping       Follow Up
Butcher   1/1/25             1/15/25            1/30/25    2/10/25
Baker     1/7/25             1/18/25            1/25/25    2/7/25
Candler   1/26/25            2/18/25            2/25/25    3/10/25

I have added conditional formatting to highlight cells in different colors depending on the current date. If the date in the cell has passed, the cell is colored red. If the date in the cell is within seven days from today, the cell shows orange. If the date in the cell is within two weeks, it shows yellow. Like that. So the idea is I can glance at the table and get an immediate visual sense of the urgency of upcoming tasks.
That is all working. But now I would like to add a way to mark a task done. If I've drafted and sent the proposal to Baker, I don’t want those cells showing up Red. That would be a distraction. I want them showing up some color indicating “complete”.

The only way I can think of to do this is to create an entire additional matrix to simply store the “done/not done” value for each cell. If this was a database, each cell would be a tuple holding a date and a completion status Boolean. But it’s not. It’s Excel.
Anyone have any suggestions for the best way to do this, such that it is simple to create new spreadsheets with these tables, and also simple to use them?

posted by Winnie the Proust to Computers & Internet (9 answers total)

Just add another column to mark x for complete, and then have conditional formatting to turn the date for those rows green, with higher priority than your other formatting/Stop when true.
posted by acidic at 9:38 AM on September 8 [1 favorite]

Is the trouble that 'draft' and 'send' are different actions, so you need a done/not done not just per row but also per column?

If so, assuming the states are consecutive, I'd add a single column for 'status'. In this case, the possible values could be 'waiting for draft'/'waiting to send'/'finished'. You can configure cells (the whole status column, minus the header) to only allow those values; that will allow you to enable a corresponding dropdown.

The conditional formatting then needs to check whether the status of this row works out to before or after completion of this part.

It would be a bit of work to set up, but then you can copy the spreadsheet as a starting point for a new one.
posted by demi-octopus at 9:48 AM on September 8 [1 favorite]

Response by poster: To clarify: I want to track the status of each cell, not each line.
posted by Winnie the Proust at 10:12 AM on September 8

If you have this split already in place it would make sense to track each action with its own column rather than mashing the conditions into some kind of single text string status.

Rather than a boolean I would instead record the date on which the action was completed in a new "completion date" column for each action. Then you can have a simple clear of formatting on the deadline columns based on the presence of data in the completion columns.

(It might not be relevant, but as a bonus, you could also set up the completion columns to have their own formatting to flag any actions completed later than the deadline for follow-up checks.)
posted by protorp at 10:56 AM on September 8

If you do decide to go with independent columns for a yes/no completion status, and are fine with just a check box, Excel now has a much easier to use checkbox feature. It's a one-click thing, where the value of the cell that has the check box is just a boolean. As far as I can tell, it's separate from the old ActiveX controls that used to be your only choice for this type of thing, and it's certainly easier to use than those ever were. Formatting is done with the text formatting controls. They can be made bigger or smaller like a font. They're really dead, simple, and even though it sounds like maybe it's not what you want to do, it might work. In this case, you would have a thin column beside every date, effectively creating your tuple. Luckily, the check box es are easy to drag and copy as they are effectively inside the cell.

If you are keen on capturing the data that protorp mentioned, I recently found a page that outlined a way to record the date and time whenever a checkbox was changed. It uses intentional circular functions, which have to be turned on per user, so if this is something that you're sharing with other people they would either all have to turn it on or just recognize that that part wouldn't work.

Speaking of which, I'm not sure what happens to those check boxes-- if they are in fact new-- when someone with an older version of Excel opens the spreadsheet. So that's something to think about too.
posted by AbelMelveny at 11:10 AM on September 8

If you had completion columns laid out in a consistent pattern next to your deadline columns, I believe you can use complex conditional formatting formulas that would accomplish what you're looking for. Might have to be separate rules, but you might be able to duplicate the rule with copy and paste formatting.

If I wanted to be able to toggle viewing the completion columns on or off other than one at a time, I'd start to get tempted to have Claude AI write me a VBA script. But not simple!

Might also be tempted to have hidden completion columns next to your deadline columns which conditional formatting uses, then have a separate table where you input completions and dates using dropdowns that refer to named ranges on your main table (say Clients and Stages). Then your hidden completion columns could look for the task on your completion table, and if it's there, display a value. Still not simple! And while you could record more info about your completion of the task, you'd be looking in two places.

Also might be tempted to revert to using tables + lookup tables to essentially create a database-like structure and use pivot tables and/or power query to create views. Still not simple!

This is tempting me to try to make something, will tell you if I do!
posted by lookoutbelow at 11:19 AM on September 8 [1 favorite]

I literally just spent the last two days building an app to do exactly this. It has projects as rows, tasks as columns, due dates in individual cells, colors based on relative dates (with red for overdue tasks, yellow for approaching due dates), and green for completed tasks. The design challenge has been the fact that each cell has two pieces of information — the date and the completion status — and you need a way to tell the app which one you are trying to change.

(This is a Mac app. I’m not a developer, am just doing this to learn how to make an app. It’s not super pretty. But if you think you’d like to try it, send me a MeMail!)
posted by wyzewoman at 5:29 AM on September 9 [1 favorite]

I... well i hate to non-answer the question but I wouldn't even do this. What i would do is add a "completed yes/no" column, use the table as a Power Query source, filter out the completed rows, un-pivot the tasks, then sort by due date ascending. So it would end up looking like:

Client Task Due
Butcher Draft-Proposal 1/1/25
Baker Draft-Proposal 1/7/25
Butcher Send-Proposal 1/15/25
Baker Send-Proposal 1/18/25
Butcher Ping 1/30/25
Butcher Follow-up 2/10/25
etc.

And have that load to a separate tab for the "hot list". Which then would be easy to color the way you say without the clutter of "done" items. And it would still be meaningful to color-blind people, which is a thing, but even I (not color-blind) hate hunting in a matrix when a simple ordered list tells me what I want to know quickly. Note that if I only care about the "Baker" project, the output table would have filter/sort headers so I could look at only that. And it would also be simple to conditionally color the hot list.
posted by ctmf at 6:01 PM on September 9

Oh rereading your question, you want each individual task to have a "done" status. Your best bet is just to do it in my suggested format in the first place.

Client / Task / Due / Done / person responsible / remarks
Butcher Draft-Proposal 1/1/25 done Dave
Baker Draft-Proposal 1/7/25 done Lisa
Butcher Send-Proposal 1/15/25 (blank) Steve waiting for copier to be fixed
Baker Send-Proposal 1/18/25 done Dave
Butcher Ping 1/30/25 Steve
Butcher Follow-up 2/10/25 Steve

Then you can sort/filter/color that as necessary. And if you REALLY wanted the matrix, that would be easy to generate with power query, remove the extra columns you don't want, pivot the data, output to a new tab. But I still don't think the color would carry over.
posted by ctmf at 6:36 PM on September 9