Anyone an excel wizard?

Doing stuff for work, and while I’m good at excel I’ve no idea how to do this or if it can be done.

I’ve got a file with a date in a column. It’s in a shit format (2021-04-10T17:43:42+00:00) this gives date and time when ordered. I want to remove the time, so from the t onwards. I can do this manually at the minute as there is only 24 rows but if this takes off there could be hundreds.

Is there a way to remove the shit I don’t want with a command/script/query? Anyone know?

Is it not just a case of formatting the whole column?

Is it an actual date or is it a string?

If it is a date then you can achieve this with formatting.

If it is a string, then you could look at the Data -> Text to Columns feature. Hightlight the column, go to the Data menu, then the Text to Columns button. Hit next twice to get to Step 3 and then select “Date” as the format. If Excel can, it will convert the string to a valid date. You can then use formatting to ignore the time element.

1 Like

Formatting the column doesn’t work. Was 1st thing I tried.

It seems to be a text field and changing to date field does nothing

Id go with notch on this. If its just a string. Then text to columns and split out from the T? Then you can format your retained date to whatever format you want?

New column with code

=MID(A1,1,FIND(“T”,A1)-1)

gives the date

=MID(A1,FIND(“T”,A1)+1,LEN(A1))

gives the time

you can look up the definitions of the functions via pressing F1

Capture2

Off to a Matlab workshop now!

4 Likes

@Zorndar thank you good sir!!

You could also use the LEFT command, which gives the number of desired characters from the left side of a cell (such as =LEFT(A1, 10) - would give the first 10 characters in cell A1)

1 Like

Just incase anyone else needs this, I got it to work using the following

=DATEVALUE(LEFT(C2,10))

That took out what I needed and converts the field to a date format which I need to group the fields for date filters