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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
Try:

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

and format as a date/time
 
Upvote 0
I'm guessing this would work also...

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

Forum statistics

Threads
1,212,929
Messages
6,110,740
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top