3 Replies Latest reply: Aug 11, 2014 6:20 AM by t quinn
Peter R. Level 4 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!

  • t quinn Level 4 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()

    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.

    Screen Shot 2014-08-10 at 10.24.42 PM.png

     

    Hope this works for you.

     

    quinn

  • Peter R. Level 4 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! :-)

  • t quinn Level 4 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