I need to create a date column based on an order number... help?

thackert

I have order numbers in the format DDMMYYYY-##### and sometimes in DMMYYYY-##### that are all populated in the same column. So, order numbers that are similar to 14Apr2023-12345 I have been able to figure out how to populate into a date column, but I have not been able to figure out how to handle it when it is 4Apr2023-12345 instead of 04Apr2023-12345. I'm not sure if I should add a column adding the zero to any dates that don't already contain it and just hide that column or if there is a way to parse the date portion of the order number into a date column as is. We are currently just inputting the date column at the same time when we input the order number but I'd like to have that autopopulate. Help please!

Paul Newcome

What is your existing formula?

thackert

In the current method for the DDMMMYYYY-#####, I am just pulling the corresponding characters using LEFT and MID functions and nested IF statements to populate a DATE function.

Day: LEFT(SR#@row, 2)

Year: MID(SR#@row, 6,4)

Month: Big nested if based on the result of MID(SR#@row, 3,3) which is basically telling it Jan = 01, Feb = 02, etc

Paul Newcome

Can you post that formula?