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.

Find more posts tagged with

Forms

Formulas

Accepted answers

All comments

AaronO

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

Macorne

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".

AaronO

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!

Macorne

@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

AaronO

Great!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.