Last Entered Value

Amber Jackson

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?

Paul Newcome

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.

Leibel S

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

Paul Newcome

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.

Amber Jackson

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

Until next week... 😄

Paul Newcome

Happy to help. 👍️