Find Minimum Upcoming Date from Multiple Columns

[Sara1]

Hi all, 

I'd like to find the minimum upcoming date out of multiple columns. My data set has 6 date columns titled Date 1 - Date 6 with dates ranging from 11/23/2021 - 11/22/2025. For each ID #(row), I want to find the minimum date after today. 

The 'Minimum Upcoming Date' column shows the date I'm trying to get.

ID	Date 1	Date 2	Date 3	Date 4	Date 5	Date 6	Minimum Upcoming Date
1	12/1/2022	11/23/2021	6/24/2023	12/1/2021	11/23/2021	12/1/2021	12/1/2022
2	1/25/2022	11/22/2021	1/28/2024	1/25/2022	11/22/2021	1/25/2022	1/28/2024
3	-	11/22/2025	11/1/2023	5/2/2022	11/22/2021	12/7/2023	5/2/2022
4	1/7/2022	9/10/2022	-	1/7/2022	11/19/2021	-	9/10/2022

I've tried various functions including this one below. This one doesn't work because it ends up finding the minimum date within the 6 columns without implementing in the >= today() function.

if([Date 1]>=today() OR [Date 2]>= today() or [Date 3]>= today() or [Date 4]>= today() or [Date 5]>= today() or [Date 6] >= today(),

rangemin([Date 1], [Date 2], [Date 3], [Date 4], [Date 5], [Date 6]))

Thank you in advance for your help.

[chrismarlow · Accepted solution]

Hi,

Maybe try;

rangemin(if([Date 1]<today(),null(),[Date 1])
,if([Date 2]<today(),null(),[Date 2])
,if([Date 3]<today(),null(),[Date 3])
,if([Date 4]<today(),null(),[Date 4])
,if([Date 5]<today(),null(),[Date 5])
,if([Date 6]<today(),null(),[Date 6])
)

Cheers,

Chris.

[Sara1]

Hi @chrismarlow, thank you! This is working perfectly. If I then wanted to find the column name for the upcoming date, what would be the best way to go about it?

Ex:

#1: Date 1

#2: Date 3

#3: Date 4

#4: Date 2

Thank you!

[chrismarlow]

Hi,

Hmmm, this looks horrible & might be a better way?

pick(
match(date(rangemin(if([Date 1]<today(),null(),date([Date 1]))
,if([Date 2]<today(),null(),date([Date 2]))
,if([Date 3]<today(),null(),date([Date 3]))
,if([Date 4]<today(),null(),date([Date 4]))
,if([Date 5]<today(),null(),date([Date 5]))
,if([Date 6]<today(),null(),date([Date 6])))
),if([Date 1]=null() or Len([Date 1])=0,makedate(1899,12,31),date([Date 1])),
if([Date 2]=null() or Len([Date 2])=0,makedate(1899,12,31),date([Date 2])),
if([Date 3]=null() or Len([Date 3])=0,makedate(1899,12,31),date([Date 3])),
if([Date 4]=null() or Len([Date 4])=0,makedate(1899,12,31),date([Date 4])),
if([Date 5]=null() or Len([Date 5])=0,makedate(1899,12,31),date([Date 5])),
if([Date 6]=null() or Len([Date 6])=0,makedate(1899,12,31),date([Date 6])))
,'Date 1','Date 2','Date 3','Date 4','Date 5','Date 6')

Cheers,

Chris.

[Sara1]

@chrismarlow , this worked perfectly as well. Thank you so much!