Date Closest to Today

System

This discussion was created from comments split from: MAX and COLLECT to find the latest date with specific criteria?.

A Rose

Hi @Genevieve P. ,

How would I use Max Collect to return the date that is closest to today, so if 1 column date @row is in 2 weeks, and another column date @row is in 3 weeks, and another column date @row was 4 weeks ago, it should always return the closest to today,

so, in the above example, how can it return the date of 2 weeks from now?

Example below,

Thanks for any help!

Genevieve P.

Hi @A Rose

You wouldn't need a COLLECT function in this instance, you can simply use MAX! 🙂

=MAX([Date1]@row:[Date3]@row)

Cheers,

Genevieve

A Rose

Hi @Genevieve P.,

Thanks for that, however, I'm looking to get not the latest date, but rather the date that is the closest to TODAY,

So, with the below example, it should return 10/18/23,

Thank you!

Genevieve P.

Hey @A Rose

My apologies, I didn't look too closely at the dates.

Try this:

=MAX(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, @cell <= TODAY())

A Rose

Hi @Genevieve P. ,

Thanks for that, however, it's still not working, see below the outcome, it gives me the earliest date, not the date that is the closest date to today,

Thanks,

Genevieve P.

Hi @A Rose

This formula excludes all dates that are in the future, then grabs the closest date to Today from all the past dates.

If that's not what you're looking for, can you explain a bit further what you mean? It may be helpful to see 3+ rows of data with the correct answers.

A Rose

Hi @Genevieve P.

Thanks for getting back to me,

I want this formula to include all past and future dates, then grab the date closest to today, no matter if it's in the past or in the future,

I'm adding some desired results highlighted below,

Thank you!

Genevieve P.

Hey @A Rose

Thank you so much, I believe I finally understand what you're looking to do! There is a way to build it all in one formula but it will be quite long. Instead, I would suggest having 3 columns:

• One Column to return the NetDays between the MAX date in the past and Today
• One Column to return the NetDays between the MIN date in the future and Today
• Final column to compare the number of days in those two columns and do one of the two formulas to grab the correct date.

I've highlighted them in blue:

First Formula - looking at Past Dates:

=IFERROR(NETDAYS(MAX(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, <TODAY())), TODAY()), "None")

Second Formula - looking at Future Dates:

=IFERROR(NETDAYS(TODAY(), MIN(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, >TODAY()))), "None")

Third & Final Formula - getting Date:

=IF(OR([Date1]@row = TODAY(), [Date2]@row = TODAY(), [Date3]@row = TODAY()), TODAY(), IF(OR([Future Date NETDAY]@row = "None", [Past Date NETDAY]@row < [Future Date NETDAY]@row), MAX(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, <TODAY())), MIN(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, >TODAY()))))

Let me know if that makes sense and works for you!

Cheers,

Genevieve

A Rose

Hi @Genevieve P.

Yes, thank you so much!!

Now, if possible can we complicate it a bit more...? 😉

I want the date columns should be selected individually so that I can move those date columns around and also exclude certain date columns throughout the sheet?

So, the formula should not be [Date1]@row:[Date3]@row, but rather each date column individually,

See an example below, I highlighted the ones to exclude...

Thank you!

Genevieve P.

Hey @A Rose

In this instance we'll want an entirely new formula.

We can use ABS to return the absolute value of a NETDAYS formula (meaning if it's negative, it will make the number positive, so we can compare Future and Past dates all together).

Here's the Nested IF statement I would use in 1 column (no helpers needed):

=IF(AND(ABS(NETDAYS([Date1]@row, TODAY())) <= ABS(NETDAYS([Date2]@row, TODAY())), ABS(NETDAYS([Date1]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date1]@row, IF(AND(ABS(NETDAYS([Date2]@row, TODAY())) <= ABS(NETDAYS([Date1]@row, TODAY())), ABS(NETDAYS([Date2]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), [Date2]@row, [Date3]@row))

=IF(AND(
ABS(NETDAYS([Date1]@row, TODAY())) <= ABS(NETDAYS([Date2]@row, TODAY())), 
ABS(NETDAYS([Date1]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), 
[Date1]@row, 

If the days between Date1 - Today are less than the days between Date 2 & Today AND less than Date 3 and Today, return Date 1.

IF(AND(
ABS(NETDAYS([Date2]@row, TODAY())) <= ABS(NETDAYS([Date1]@row, TODAY())),
ABS(NETDAYS([Date2]@row, TODAY())) < ABS(NETDAYS([Date3]@row, TODAY()))), 
[Date2]@row, 

OTHERWISE, If the days between Date2 - Today are less than the days between Date 1 & Today AND less than Date 3 and Today, return Date 1.

[Date3]@row))

Otherwise, Date 3 must be less than the other two, so return Date 3.

Cheers,

Genevieve

A Rose

Hi,

Sorry for my late response,

Thanks so much for your help, this works amazing!

What if I want to add additional date columns to the formula, does the formula need to be revised completely?

Thanks,

Genevieve P.

Hi, no problem!

Yes, if you're adding more information we'll need to update the formula. How many columns do you need?