Min Date Same Field - Comma Separated if Multiple

I have a field that may have 1 or more dates in the format MM/dd/yyyy. Some have 1 date, while some may have more than 1 date. See Input file attached.

The end goal is to get the earliest date in column B.

While I found this in Community, I believe the problem is that since A & C only have 1 date while B, D, E have multiples, the DateTime tool could not convert A & C (I believe the ones with 1 date is in a date format while the ones with multiples are string?). I've attached a sample workflow below. How do I get to my end result of:

Field	Date
A	03/07/2024
B	5/10/2024
C	2/28/2024
D	10/11/2024
E	12/11/2023

Help please! Thank you!

Input.xlsx

MinDateExample.yxzp

Date Time

Accepted answers

DataNath

Hey @Cathy_L, how does this look? I've just used a Formula expression to check whether the date is already in a true format, and skip parsing if so, otherwise parse it out from the mm/dd/yyyy format.

MinDate.yxzp

All comments

OTrieger

@Cathy_L
You can use Text to Columns tool and set it to Rows in stead of Columns in this way you will have all the dates in a single cell and then with the use of Summarize tool you can group it with the selection of min date.

DataNath

Hey @Cathy_L, how does this look? I've just used a Formula expression to check whether the date is already in a true format, and skip parsing if so, otherwise parse it out from the mm/dd/yyyy format.

MinDate.yxzp

Cathy_L

Thanks @OTrieger - that is actually how I have it set up: Input > Text to Columns (rows) > Data Cleansing to remove whitespace > DateTime to convert from String to Date > Summarize to group by Field with min date.

Post Data Cleansing, what I see is that the ones with 1 date change has this format yyyy-MM-dd while the ones that had multiple dates and were split into rows by Text to Columns are in the format MM/dd/yyyy. When the DateTime tool tries to convert all dates into Date, it is resulting in conversion error:

DateTime (7) DateTime_Out: Cannot convert "2024-03-07" to a date/time with format "%m/%d/%Y" and language "English": Month number is out of range 1..12: '2024-03-07' Record #1

Thank you for your suggestion!

Text to Columns.png

Cathy_L

Thanks @DataNath ! Let me check this out. The one thing I'm thinking is if the string is not in chronological order, it does not seem to check for the minimum date (apologies, I should have created an example for that).

However, I believe I could use your formula to bifurcate the ones that are in the true format vs the ones that are not, perform the previous workflow only on those that are not in true format, and rejoin them. Not sure if there's a more graceful way of building this, but may give that a try if there's no better solution. Thank you!

DataNath

Hey @Cathy_L not sure what you mean here? The Summarize tool will look for the earliest date by using the Min - the dates don't need to be in any particular order unless you're using something like First.

Cathy_L

I missed something but looking at it again, you are correct! I've accepted your initial post as the solution. THANK YOU SO MUCH!! You are amazing!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703