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 to any 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 to any 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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive