IF statements with multiple dates

tmgraham
edited 12/09/19 in Smartsheet Basics

I am trying to have multiple columns with multiple dates automatically update my "next due date" column. For example I have 4 columns with 4 different dates, I want my due date column to be the last date + 90 days. 

For example if I have columns, A,B,C,D, and E, and all of those columns have a date except E, I would want my due date to go off of Column D + 90 days, but then when I do input a date into column E I would want my due date column to automatically update + 90 days from that date.

Any help that can be offered would be appreciated.

 

Thanks

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Will the dates always be in chronological order from left to right, or could the date in column E be an earlier date from column D?

     

    If it is always chronological (E always greater than D, D always greater than C, etc...), you could use:

     

    =MAX([First Date Column Name]:[Last Date Column Name]) + 90

     

    If the dates can be in any order, and you want to pull from the last column populated, it is possible but requires just a little more creativity. I've built something very similar before, but I can't remember exactly how I did it off the top of my head.

     

    If that's what you need let me know, and I'll work on putting it together for you.

    thinkspi.com

  • The usually are in chronological order. I guess if I needed anything it would need to be off of the farthest out date. I attached a picture. So out of the PM columns it would need to be which ever date is the farthest out would decide the Next due date. I have played with some IF functions, but I haven't had to much luck with that.

     

    Thanks for helping with this! I really appreciate it.

    Example spread.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If all you need is 90 days after the latest date, the MAX function above will do the trick for you.

    thinkspi.com

  • Yup that did work. Don't know why it wasn't cooperating at first! 

     

    Thank you so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad I could help.

    thinkspi.com