Forum Discussion
How do I change this date (Nov 1, 2017 1:57:53 PM PDT) to 11/1/17?
Hi there.
I'm challenged with this date information: Nov 1, 2017 1:57:53 PM PDT (from Amazon data) and need to change it into a sortable date of 11/1/17. I cannot sort A-Z with this format. I have a huge list of these dates. I need to change them and have no problem putting that information into another column for my spreadsheet. I've been looking for a solution for several hours. Hopefully there's a one-step solution. I'm tired of manually doing it. I have too many cells to change. It is not in a date format and changing the cell to a date format doesn't do a thing.
Thanks in advance for your help.
| date/time |
| Nov 1, 2017 1:57:53 PM PDT |
| Nov 1, 2017 11:13:33 AM PDT |
| Nov 1, 2017 12:51:41 AM PDT |
| Nov 1, 2017 3:48:07 AM PDT |
| Nov 1, 2017 7:45:52 PM PDT |
| Nov 1, 2017 9:22:37 AM PDT |
| Nov 1, 2017 9:50:20 AM PDT |
| Nov 2, 2017 12:19:31 AM PDT |
| Nov 2, 2017 12:50:30 AM PDT |
| Nov 2, 2017 7:06:43 PM PDT |
| Nov 2, 2017 9:03:10 AM PDT |
| Nov 2, 2017 9:48:30 PM PDT |
| Nov 2, 2017 9:48:30 PM PDT |
| Oct 20, 2017 2:16:00 PM PDT |
| Oct 20, 2017 9:53:44 PM PDT |
| Oct 21, 2017 1:52:06 AM PDT |
| Oct 21, 2017 12:07:57 PM PDT |
| Oct 21, 2017 2:05:58 PM PDT |
| Oct 21, 2017 7:30:53 AM PDT |
| Oct 21, 2017 9:02:28 PM PDT |
| Oct 22, 2017 2:54:23 PM PDT |
| Oct 22, 2017 9:33:21 PM PDT |
| Oct 22, 2017 9:58:13 PM PDT |
| Oct 23, 2017 11:09:42 AM PDT |
| Oct 23, 2017 12:37:20 PM PDT |
| Oct 23, 2017 2:13:26 PM PDT |
| Oct 24, 2017 1:19:09 PM PDT |
| Oct 24, 2017 1:22:13 PM PDT |
| Oct 24, 2017 12:57:31 PM PDT |
| Oct 24, 2017 3:02:46 PM PDT |
| Oct 24, 2017 3:02:50 PM PDT |
| Oct 24, 2017 3:15:14 PM PDT |
| Oct 24, 2017 3:15:14 PM PDT |
| Oct 24, 2017 3:15:14 PM PDT |
5 Replies
Hi Christine,
Try to use this formula:
=DATEVALUE(SUBSTITUTE(A2,"PDT",""))
You may get the dates in the serial number mode as shown in the above screenshot, in this case, you can convert the format of the cells to the date format that you want as follows:
Step 1
Highlight all the cells that contain these serial numbers, then press Ctrl + 1.
Step 2
Select Date Category.
Step 3
Choose the type that you prefer, then hit OK.
Hope this helps.
PERFECT!! It worked. Thank you so much for taking the time to help me.
I have a similar issue, but this solution is not working for me. Here is my example:
2021-12-09, 5:54:47 PST
About 40K rows! Thanks.
