CODEWITHSUNDEEP
excelstringdatetimetimestamp
ChrisB
ChrisB

Reputation: 2048

How to turn a Timestamp string into DateTime format in Excel?

I have a string such as 10/21/19 5:45:40 AM +00:00 in a CSV and would like to create a pivot table to see how many times the door has been opened per hour.

This only works when Excel understands the Timestamp string as a timestamp.

I've tried =DATEVALUE() and formatting but Excel still can't make sense of the string.

I'm in UK locale, so dd/mm/yyyy.

What's the best way to get this to work? Thanks!

Excel Time Series

Upvotes: 0

Views: 1517

Answers (2)

Terry W
Terry W

Reputation: 3257

This can be done using Text to Columns function under the Data tab.

I used the following data for demonstration:

| Timestamp                   | Sensor    | Action |
|-----------------------------|-----------|--------|
| 10/21/19 5:45:40 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:37:12 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:38:15 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:39:23 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:40:27 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:41:28 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:42:30 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:43:32 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:44:35 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:45:36 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:48:38 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:47:41 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:48:41 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:49:42 AM +00:00  | Shop Door | Open   |
| 10/22/19 9:53:11 AM +00:00  | Shop Door | Open   |
| 10/22/19 10:07:30 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:16:32 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:22:27 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:23:27 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:24:29 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:25:58 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:27:03 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:28:09 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:29:15 AM +00:00 | Shop Door | Open   |
| 10/22/19 10:30:20 AM +00:00 | Shop Door | Open   |

Steps are:

  1. Highlight Column A which is the Timestamp column;
  2. Click Text to Columns button under the Data tab;
  3. Select Fixed Width, click next;
  4. Left click somewhere between the time and +00:00 to insert a column break, click next;

Insert Col Break

  1. Highlight the first column which is date, and set the format to MDY under Date, leave the format of the other two columns as default, click Finish;

MDY

  1. It is OK to overwrite the data in Column B and C given that they are irrelevant in structuring the pivot table;
  2. Highlight the new table (including headers) and insert a pivot table, put the Timestamp and Sensor under Rows filed, and put Action under the Values field, then you should have something like the following:

Pivot Table

Here is the Fields setting:

Fields

Let me know if you have any questions. Cheers :)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

You need to remove the +00:00 part of the string:

=--(LEFT(A2,LEN(A2)-6))

Then format the cell as desired like : mm/dd/yyyy hh:mm:ss

enter image description here

If your local date is dd/mm/yyyy use this:

=DATE(20&TRIM(MID(A2,FIND("|",SUBSTITUTE(A2,"/","|",2))+1,2)),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),LEFT(A2,FIND("/",A2)-1))+MID(LEFT(A2,FIND("+",A2)-2),FIND(" ",A2)+1,99)

And format as desired.

enter image description here

Upvotes: 1

Related Questions