Converting a text date: Thu, Aug 12, 2021 into real date
Hi Everyone,
could anyone assist me with converting a text date column formatted like this: Thu, Aug 12, 2021 into a Date column?
For example, I have two columns.
- text date column where I am importing data in this format: Thu, Aug 12, 2021
- Real date column that forces a "real date"
I know I can use the =DATE(VALUE(LEFT([text date])@row .... VALUE(MID([text date])@row ...VALUE(RIGHT([textdate])
I am just having a hard time piecing it all together as I probably would have to also convert the months into actual numbers right right? Something like Jan = 01 , Aug=02 etc..
Thanks for any help!
Best Answer
-
You are correct - you will need the months to be actual numbers and I would recommend making a look up table for the month.
**I made an assumption that all the months will be 3 characters, if not, may need to tweak it a little bit**
Parsed out the formulas are the following:
Year: =VALUE(RIGHT([Primary Column]@row, 2))
Month: =VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false))
Day: =VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))
If you wanted to go directly from Thu, Aug 12, 2021 to 8/12/21 the formula is below:
=DATE(VALUE(RIGHT([Primary Column]@row, 4)), VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))
I would just put the month name to number conversion on a separate sheet and insert reference in the VLOOKUP formula to make it easier :) (Would replace [Column3]3:[Column4]14 with whatever your reference sheet range is)
Also, make sure you final date value column is set to DATE instead of Text/Number.
Let me know if you have any additional questions!
Best,
Lidiya Shutaya
Answers
-
You are correct - you will need the months to be actual numbers and I would recommend making a look up table for the month.
**I made an assumption that all the months will be 3 characters, if not, may need to tweak it a little bit**
Parsed out the formulas are the following:
Year: =VALUE(RIGHT([Primary Column]@row, 2))
Month: =VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false))
Day: =VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))
If you wanted to go directly from Thu, Aug 12, 2021 to 8/12/21 the formula is below:
=DATE(VALUE(RIGHT([Primary Column]@row, 4)), VALUE(VLOOKUP(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Primary Column]@row, FIND(",", [Primary Column]@row) + 6, 2))
I would just put the month name to number conversion on a separate sheet and insert reference in the VLOOKUP formula to make it easier :) (Would replace [Column3]3:[Column4]14 with whatever your reference sheet range is)
Also, make sure you final date value column is set to DATE instead of Text/Number.
Let me know if you have any additional questions!
Best,
Lidiya Shutaya
-
Hey Lidiya,
thanks so much for the help. So I am just going to Freeze and hide my look up column to convert.
I am trying to apply it as a formula for the entire column:
=DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2)))
If I paste it individually into the cell, i get the the INVALID DATA TYPE error because the Imported Data is blank. I also can't apply it as a column formula because of a syntax error
-
Hello,
Putting an IFERROR option should eliminate the blank rows coming up as #invalid data type.
=IFERROR(DATE(VALUE(RIGHT([Test 1]@row, 4)), VALUE(VLOOKUP(MID([Test 1]@row, FIND(",", [Test 1]@row) + 2, 3), [Column3]4:[Column4]15, 2, false)), VALUE(MID([Test 1]@row, FIND(",", [Test 1]@row) + 6, 2))), "")
For the column formula issue - column formulas do not allow specific cell reference. It will allow @cell, @row, or a referenced range.
=DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), [Column3]3:[Column4]14, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2)))
If you put the lookup table in a separate sheet and use the "Reference sheet" feature, the syntax error should be resolved.
Let me know if you add any additional questions!
-
I hope you're well and safe!
I'd recommend testing with a simpler formula first and convert it to a column formula and then change it to the other one and see if that works.
Did that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Just wanted to follow up and mentioned that it's working!
Made a separate sheet like Lidiya mentioned and just called it from my main sheet.
=DATE(VALUE(RIGHT([Imported Date]@row, 4)), VALUE(VLOOKUP(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 2, 3), {Date converter Range 1}, 2, false)), VALUE(MID([Imported Date]@row, FIND(",", [Imported Date]@row) + 6, 2)))
Thanks you Lidiya! Also appreciate the input Andree!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!