# Last Entered Value

Options
✭✭✭✭✭

Good morning gurus!

I have another puzzle for you. I have several date columns that may or may not be filled out depending on the project (row). I need to calculate a date for 6 months beyond the last entered value in the date columns (see pic below). Any suggestions?

## Best Answer

• ✭✭✭✭✭✭
Answer ✓
Options

Slight adjustment to @Leibel S's first formula. In that formula, the COUNT is generating a number for the row number and not the column number.

=INDEX(COLLECT([36-Month]@row:[60-Month]@row, [36-Month]@row:[60-Month]@row, @cell <> ""), 1, COUNT([36-Month]@row:[60-Month]@row))

I personally would just use a MAX function for this one.

=MAX([36-Month]@row:[60-Month]@row)

The formula for adding 6 months should work though.

## Answers

• ✭✭✭✭✭✭
Options

Below formula would get you the last date.

=INDEX(COLLECT([36-Month]@row:[60-Month]@row, [36-Month]@row:[60-Month]@row, @cell <> ""), COUNT([36-Month]@row:[60-Month]@row))

Adding 6 months to that use the formula below (idea courtesy of @Paul Newcome):

=IFERROR(DATE(YEAR([Last Date]@row), MONTH([Last Date]@row) + 6, DAY([Last Date]@row)), DATE(YEAR([Last Date]@row) + 1, MONTH([Last Date]@row) + 6 - 12, DAY([Last Date]@row)))

• ✭✭✭✭✭✭
Answer ✓
Options

Slight adjustment to @Leibel S's first formula. In that formula, the COUNT is generating a number for the row number and not the column number.

=INDEX(COLLECT([36-Month]@row:[60-Month]@row, [36-Month]@row:[60-Month]@row, @cell <> ""), 1, COUNT([36-Month]@row:[60-Month]@row))

I personally would just use a MAX function for this one.

=MAX([36-Month]@row:[60-Month]@row)

The formula for adding 6 months should work though.

• ✭✭✭✭✭
Options

@Leibel S @Paul Newcome you guys are amazing! Thank you so much for your help!!

Until next week... 😄

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!