Formula to auto fill date based on last non blank

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.
Comments
-
=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
-
=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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!