I need to create a date column based on an order number... help?
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!
Answers
-
What is your existing formula?
-
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
-
Can you post that formula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!