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?

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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.

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.

GrantSmith

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.