Date Format Conversion

I have a spreadsheet coming from an outside source that I do not create, but I am creating Domo cards with it. Right now the dates are formatted in a way where I can't sort them. I just need to drop the day of the week at the beginning and the time at the end of each date row.
Current Format: Mon, 1/12/2020 - 12:00
Format I need: 1/20/2020
Ultimately I want to be able to upload updated versions of that same spreadsheet every week, but have the data look how I need it to without having to convert it manually in Excel each time.
Any ideas?
Best Answer
-
If you are able to use Magic ETL, you can use the Split Columns tile to eliminate the extra information and just give you the date.
Add a Split Columns tile and choose Comma for the delimiter to split on. In step 4 of the tile, name this first column Day, then add another column and call it Date+Time.
Next, add another Split Column tile but this time choose Custom and then enter your hyphen for the delimiter to split on. In step 4, name the first column Date and then add another column and call it Time.
To be safe, I would add a String Operations tile after this and apply the trim function to the newly created Date column.
Next, you can add a Set Column Type tile and set the Date column to a Date data type.
This should get you the date information by itself.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
If you are able to use Magic ETL, you can use the Split Columns tile to eliminate the extra information and just give you the date.
Add a Split Columns tile and choose Comma for the delimiter to split on. In step 4 of the tile, name this first column Day, then add another column and call it Date+Time.
Next, add another Split Column tile but this time choose Custom and then enter your hyphen for the delimiter to split on. In step 4, name the first column Date and then add another column and call it Time.
To be safe, I would add a String Operations tile after this and apply the trim function to the newly created Date column.
Next, you can add a Set Column Type tile and set the Date column to a Date data type.
This should get you the date information by itself.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Another option would be to utilize the replace text tile and use a regular expression:
^.*(\d{1,2}\/\d{1,2}\/\d{4}).*$
You'll put the above regular expression in the step in box 2. Also make sure to click the gear icon and select "Use RegEx".
In step 3 place $1 as the replacement text.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 305 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 111 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 782 Beast Mode
- 76 App Studio
- 43 Variables
- 738 Automate
- 186 Apps
- 473 APIs & Domo Developer
- 65 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 138 Manage
- 135 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive