Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
2  
You can use conditional formatting to highlight cells according to values contained or computed – however, to answer this, it would be good to know on which OS you are running Excel (the Windows and Mac versions differ), which version it is and what exactly you mean by “every three days”. –  kopischke Oct 24 '11 at 7:30
    
I am running office 2010/2011 for mac. by three days I mean, the cell should be highlighted first time (first time date should be specified somewhere) on monday, then three days later, on thursday, and so on –  flow Oct 24 '11 at 9:46

1 Answer 1

up vote 3 down vote accepted

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):

Screenshot of the Excel:mac 2011 conditional formatting dialog maze

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:

Screenshot of highlighting in action

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…).
share|improve this answer
    
Formula translated thanks to Excel 2007 function name translations. –  kopischke Oct 24 '11 at 11:25
    
Updated formula to actually do what it is meant to. Stupid me. –  kopischke Oct 24 '11 at 13:52
    
you are the genius, thanks a lot! –  flow Oct 24 '11 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.