Highlight Excel spreadsheet cells every three days

Question

I use Excel 2011 on OS X to track down my tasks list.

Now I would like to add a date cell to some tasks so that every three days these tasks get highlighted. My question is if there is any way so that a cell checks against the real date and it gets somehow highlighted when the date follows some conditions, for instance, every three days.

If you think OpenOffice Calc or another similar spreadsheet system is better than Excel for this, please let me know.

Answer 1

This can be done using conditional formatting. Mark all cells you want to highlight, starting in the upper left corner of the range. Select “Conditional Formatting…”, add a format (“+” button), select “Classic” in the upper drop down, then “Use a formula …” in the one below that (names may vary slightly from what I indicate here, as I am using a German system, but the screenshot should help you get around):

In the formula input field, enter the following:

=IF($A1<=TODAY(),MOD(DATEDIF($A1,TODAY(),"D")-IF(WEEKDAY($A1,2)=1,0,7-WEEKDAY($A1,2)+1),3)=0,FALSE)

This will highlight the selected rows on the first monday after the due date (or on the due date if it is scheduled for a monday – it does not highlight every monday after that, as mondays do not repeat as a multiple of 3 days), then every three days after that first occurrence:

Notes:

• Replace $A1 by the correct column and row for the first date field in your selected range.
• Adjust formatting to your preferred way of highlighting your rows at the bottom of the dialog (OT: I don’t know why MS makes this a maze of drop down selectors…).