Help converting a date with a timestamp in excel

acaldwe13

New Member
Joined
Feb 3, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,266
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try:

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

and format as a date/time
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
I'm guessing this would work also...

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

Watch MrExcel Video

Forum statistics

Threads
1,127,313
Messages
5,623,941
Members
416,001
Latest member
teabag

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top