Last Entered Value
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
-
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
-
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)))
-
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 @Paul Newcome you guys are amazing! Thank you so much for your help!!
Until next week... 😄
-
Help Article Resources
Categories
Check out the Formula Handbook template!