Produced consistent dates from mix of single and double digit entries?

Hello,

EDIT - The issue I was having is due to the import step of tab/comma-delimited data. I imported all columns as general and thus the zeroes in many dates were removed. Changing this option during the import wizard (page after selecting your delimiter type) to "text" rather than "general" imported the DDMMYY values correctly.

I am attempting to produce a time series of different measurements and subsequently import to other software that requires specific date formats.

The timeseries data I have has a mix of single digit and double-digit month values (DDMMYY, DMMYY, DDMYY, DMYY)

Example
120288 reads correctly as 12/02/88
10889 reads as 10/88/89
22889 reads as 22/88/89

While the YY dates are always fine, I'm unsure on how to read values to another cell as the month values will have a different position within the cell depending on the date. I guess a complex IF function could sort it out, but I'm not sure of how to do so, having read a few similar problems.

Any help or links to solutions would be appreciates while I continue searching.

Best wishes

K

That's a great point; I will check to see if any instances have a 0 inserted, I expect not. It may be a layover of the output I'm using being generated from a NetCDF file that has separate numerical columns for each D M Y

Edit: Using a NetCDF viewer day, month and year are separate columns and the online data portal I've used to get .csv of the data has merged the three with these frustrating results. I think exporting the columns individually from the NetCDF, correcting to two-digit dates then combining them would be simplest.

Answer:
1) Parse your day, month and year into different columns
2) In a new column combine the day month and year into the dd/mm/yyyy format using &"/"& between each number. It will now look like a date.
3) In a new column add zero to each of the date-like entries above. This will produce actual dates that you can format according to your preferred date format.

See the picture below which shows how I did it.

Screenshot 2022-06-07 at 15.48.52.png

You may have to adapt the formula if you don't use European style dates, or if you don't use "/" as your date separator.