Check list with due dates & cell highlighting

Question

Peter R. Author

Level 4

1,222 points

Check list with due dates & cell highlighting

Hi,

I've searched for such a thing for an hour now but I couldn't find it:

I have a check list that I want to combine with variables and cell highlighting.

E.g.

Task 1 Due today [fixed, not updating]

Task 2 Due 1 week later

Task 3 Due 2 weeks later

Task 4 2 weeks before date XX/XX/XXXX

Task 5 1 week before date XX/XX/XXXX

When the due dates are reached, I'd like the cell to turn red; when I check the task as done, I'd like the red to disappear again.

Thanks a lot for a hint!

PS: I have also searched another hour for a todo / project management app for Mac OS or iOS that could handle variables and let me copy task lists / projects. I haven't found one. If anyone has heard of one, please let me know!

Posted on Aug 10, 2014 8:53 AM

Reply

Answer 1

Best reply

t quinn

Level 5

7,043 points

Aug 10, 2014 9:45 PM in response to Peter R.

Hi Peter,

Here is a first attempt. too many nested IFs for my taste but I haven't seen a way around them yet.

A1=TODAY()

A2, A3, etc. your todos.

In column C you need to enter today's date. SGIII has an automator service that will insert today's date. It shouldn't be hard to find. This is the only way I know to do a time/date stamp that is persistent.

D2=IF(B2,"complete",IF(E2,C2,IF(F2,C2+7,IF(G2,C2+14,IF(LEN(H2)>0,H2+14,IF(LEN(I2 )>0,I2+7,"choose a due date"))))))

Highlighting rule for D2 is Numbers> less than $A$1 red fill.

Hope this works for you.

quinn

Reply

Answer 2

Peter R. Author

Level 4

1,222 points

Aug 10, 2014 11:55 PM in response to t quinn

Hi,

thank you very much! I guess there's really too many IFs for me; and one more: Only IF I had a clue I would be able to reproduce that! :-)

Reply

Answer 3

t quinn

Level 5

7,043 points

Aug 11, 2014 6:20 AM in response to Peter R.

Hi Peter,

I think I am getting better at this formula writing. I click and let Numbers do most. here is what the formula says:

IF B is checked print "complete" as due date otherwise;

IF E is checked print C as due date otherwise;

IF F is checked print C plus 7 as due date otherwise;

IF G is checked print C plus 14 as due date otherwise;

IF H has an entry print H plus 14 as due date otherwise;

IF I has an entry print I plus 7 as due date otherwise;

Print "Choose a due date" as due date.

I think that is the most nested IFs I have ever used. Good luck!

quinn

Reply