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.

Find more posts tagged with

Formulas

Comments

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

Jeeppler

This is turning up #unparseable for me

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

Sign in to join the conversation!

Quick Links

Trending discussions

Combining a few columns into one --can I nest IF statements?
My current formula is =IF([To whom will the sample go?]@row = "Customer/Shipped out", "Shipping Date: " + [Date to be Shipped]@row + CHAR(10) + [Ship Method]@row + CHAR(10) + CHAR(10) + [TR Full Contact Info]@row , "") I need this to be able to look at [TR Full Contact Info]@row and if blank, pull the information from [IN…
Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)