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... 😄
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.4K Get Help
- 447 Global Discussions
- 144 Industry Talk
- 478 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 151 Just for fun
- 72 Community Job Board
- 490 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!