How to flag the second largest date in a sheet
I have a sheet that collects responses from a form, specifically collecting status. I pull this data in to a second sheet to be able to show current and previous status on the same line. To do this, I am creating a helper column to flag the second largest date. I know how to find the most recent entry (largest date) using a combo of MAX and COLLECT, but the LARGE formula (=LARGE([Date number]:[Date number], 2)) has not been successful for me, to find the second largest date (in order to record previous status).
Does anyone have a way to find the second largest date?
Picture of headings here for context. The "date number" column is my attempt to format the date as a text/number in order to make the LARGE formula work. My assumption is that the column formula (which I need because this is a form) is messing with the formatting.
Answers
-
This worked for me:
Here, the "Date" column is a date type column, and the Testing Result column is also date type. There's no need to convert anything or use COLLECT - the MAX and LARGE formulas seem to work directly on dates.
I tried this with the automatically generated "Date Created" column and it seemed to work fine though since they were all created today the answer was always the same - but it didn't complain.
Aaron
-
Thanks @AaronO, unfortunately this is not working for me. I am getting an "Invalid Column Value" error when I reference the original (auto-populated) date/time column "Created", and then an "Invalid Value" error when I use the column which has the date reference column formula "Date Number".
-
Hi @Macorne,
Hm. Are you sure the [Previous entry] column is a Date column? I get "Invalid Column Value" when I try to put a date into a text/number column and vice-versa. Since LARGE([Created]:[Created],2) produces a date, it needs to be in a Date column.
Further, I am able to make the LARGE function work on the [Created] column itself, without trying to reformat it as a text/number. What's the formula you're using in the [Date number] column? If you're converting the date into a string that just looks like a date, I doubt LARGE will recognize it as something it can sort. When I try to use LARGE on a string (for instance, using (=TODAY()+"") to make a string representation of today's date) I get an Invalid Value error.
But... will this even get you what you want? If you did get LARGE to work in the [Previous Entry] column, every line would have the same value, which is just the second-latest date in the Created column. They'll all be the same. And, in this case, they'll all say 2/28/23 because a Date column doesn't show the time, just the date and since both entries in the Created column were created on the same day, they both look like 2/28/23. And worse, every time a new entry came in, the [Previous Entry] for every line would change.
So do I understand what you're trying to do? When a new status comes in through the form, you want to put the date of the previous recent status in the [Previous entry] column on the new line? You mentioned using MAX and COLLECT - doesn't this give you what you want?
=MAX(COLLECT(Created:Created, Created:Created, <Created@row))
That will just find the largest date/time in the Created column which is less than the current row's Created value. You could make that a column formula (but again the column must be a Date type).
Sorry if I'm making things more confusing rather than less!
A
-
@AaronO bingo, you're right. I was trying to use a text/number column type. Thank you for the help! The Large worked on the created date column when I changed the column type to date
-
Great!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!