Help converting a date with a timestamp in excel

[acaldwe13]

Hello! In my office we use slack for the majority of our communication needs. With Slack you can build workflows that allow users to enter data which can then be imported into an excel sheet. Slack date/timestamps each submission in the excel file in a format which looks like this: "2020-11-11T19:59:57Z." Now this wouldn't be an issue, but there are hundreds of entries every day which have to be filtered to the date in order for us to properly analyze the information. Does anyone know if there is a way a way to convert this to a actual date so that I can filter information properly? I have tried =TEXT(A2, "mmmm, yy") and other variations, however all it does is enter the same details into the new cell without converting anything. Any help would be greatly appreciated!

 

[Norie]

Select the column with the datas, goto Date>Text to columns..., select Delimited and set that delimiter as T.

On the 3rd step set the Column data format to YMD and choose Do not import column for the second column, click FInish.

Alternatively, if you want to keep the time, again goto Data>Text to columns but this time select Fixed Width.

On the second step put a line break before and after the T and before the Z.

On the third step choose YMD for the column data format for the first column, choose not to import the 2nd and 4th columns, click Finish.

 

[RoryA]

Try:

=SUBSTITUTE(SUBSTITUTE(A2,"T"," "),"Z","")+0

and format as a date/time

 

[acaldwe13]

Try:

=SUBSTITUTE(SUBSTITUTE(A2,"T"," "),"Z","")+0

and format as a date/time

This worked!

 

[Rick Rothstein]

I'm guessing this would work also...

=LEFT(SUBSTITUTE(A2,"T"," "),19)+0