Welcome!

Return the latest date an event happened

Hello,

I would like some help to return the latest date an event happened that was hosted by a person.

In one smartsheet I have the data entry table, and another that I'm using as a summary table to reference to for convenience.

For example I'm trying to return.

e.g. 'when was the last date 'Joe Bloggs' hosted an event?' . I would want '28/03/2018' returned in the formula out of the list below:

Joe Bloggs 24/03/2018

Joe Bloggs 25/03/2018

John Jones 26/03/2018

Joe Bloggs 28/03/2018

Vlookup doesn't work for me (unless using it wrong) because it cannot return the latest date value.

Is there some way to find this with a formula?

Thanks.

Find more posts tagged with

Comments

p_bureau

Hi,

Since there is no array formula such as in Excel, nor a "MAXIFS" formula, you won't be able do to this unless you first sort your data (decreased order).

Paul.

Chris McKay

Hi CamNZ,

While Paul is correct that Smartsheet does not support array and there is no MAXIFS operator, there are definitely options available that don't rely on you filtering or sorting the data.

Try this:

=MAX(COLLECT(Date:Date, Name:Name, [Report Name]1))

Where the [Report Name] column contains the criteria you are matching against (i.e. the person's name).

Hope this is what you were after. Kia ora bro!

CamNZ

Hello Chris,

Thanks for that solution - that worked!

All sorted now.

Thanks.

Chris McKay

Hello CamNZ,

You are more than welcome. Glad I could assist.

Alfredo ARTEAGA

I tried something like this, but I am getting #INVALID COLUMN VALUE error.

Looks like the output is a date, so it cannot be in a text/number column. If I try to write it in a date column, it says is invalid value and adds a ' before.

Jeff Zumaris

If you write the formula in a column that is not a date you will receive that error. To circumvent that add +"" on the end of your formula and you will get the result. One thing to note when done this way you will also get a Time Stamp with it along with the date.

Sign in to join the conversation!

Quick Links

Trending discussions

Why am I not getting reminders?
My reminder in smartsheet automation is set to trigger every day starting 12/05/2025 at 8am (gmt+1) and the condition is to send the reminder to the contact in the contact list if the reminder flag is set to 1 and the status is not one of DONE. Currently on the sheet, a task has the reminder flag set to 1 and the status is…
I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Membership/ Access question
Hi Everyone, My IT has a lot of confusion on what we access we really need to have for certain people. What is the difference between a "Member" and a "Licensed User?" We have a lot of sheets that have owners who do all of the changes of the sheet. I know that they need to be licensed users. But then, we have a lot of…