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


gives the date


gives the time

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


Off to a Matlab workshop now!


@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


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