Converting a date in a text type to Date type
Hi, I have a column as text/number type with date value such as '2023-06-27 09:02:53' and I want to have another column and convert it to DATE column type.
e.g "Date as Text Type" column is a text/number field. I want to convert this to DATE and put in the "Date as DateType" column.
is there a way to do it?
Best Answer
-
You will need to make sure your [Date as DateType] column is formatted as Date. This also assumes your [Date as Text Type] column is always in the same format. (Starts with full 10 digit date)
=IF([Date as Text Type]@row <> "", DATE(VALUE(LEFT([Date as Text Type]@row, 4)), VALUE(MID([Date as Text Type]@row, 6, 2)), VALUE(MID([Date as Text Type]@row, 9, 2))), "")
Answers
-
You will need to make sure your [Date as DateType] column is formatted as Date. This also assumes your [Date as Text Type] column is always in the same format. (Starts with full 10 digit date)
=IF([Date as Text Type]@row <> "", DATE(VALUE(LEFT([Date as Text Type]@row, 4)), VALUE(MID([Date as Text Type]@row, 6, 2)), VALUE(MID([Date as Text Type]@row, 9, 2))), "")
-
Thank you very much Carson. it works perfectly. Appreciate your quick help!
-
What is the purpose of the Value function, here?
-
I hope you're well and safe!
VALUE converts the text date to a number.
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, Awesome, 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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives