3 Replies Latest reply: Aug 11, 2014 6:20 AM by t quinn
Level 4 (1,190 points)

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!

• Level 4 (3,370 points)

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

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

• Level 4 (1,190 points)

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

• Level 4 (3,370 points)

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