Formula to auto fill date based on last non blank

Jeeppler
Jeeppler
edited 12/09/19 in Formulas and Functions

I figured out the formula to give me exactly what I need in Excel:

=IF(LOOKUP(2,1/(A1:D1<>""),A1:D1),EOMONTH(LOOKUP(2,1/(A1:D1<>""),A1:D1),3))

 

This formula does not translate to smartsheet. I need a formula that finds the last non blank in the row and auto fills a due date of 3 months later, last day of the month.

 

 

Tags:

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    =DATE(YEAR(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)), MONTH(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)), DAY(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)))

    Was working on this and realized I have somewhere to be so here is what I came up with. This grabs the Date of the correct cell. 

     

    =INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)

    This grabs the last non-blank and separates into Day Month and Year Values.

    =DATE(YEAR(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)), MONTH(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)), DAY(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)))

  • This is turning up #unparseable for me

  • L_123
    L_123 ✭✭✭✭✭✭

    =MONTH(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)) + "/" + DAY(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1)) + "/" + YEAR(INDEX(A2:F2, 1, MATCH("", A2:F2) - 1))

     

    Posts the cell you are looking for in Month/Day/Year

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!