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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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
38,782
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
37,881
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I'm guessing this would work also...

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,012
Messages
5,834,914
Members
430,326
Latest member
tomwax46

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