Forum Discussion
How do I change this date (Nov 1, 2017 1:57:53 PM PDT) to 11/1/17?
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.
- Heather1645Feb 09, 2022Copper Contributor
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.
- MohdEzanJun 21, 2022Copper Contributor
Heather1645I need to change the year because I typed it incorrectly, but the rows are about 18k. Any ideas on how I can make it easier to replace the year from 2021 to 2022 using Excel?
12/5/2021 1:17:46 AM 12/5/2021 1:24:07 AM 12/5/2021 1:24:30 AM 12/5/2021 1:24:17 AM 12/5/2021 1:24:40 AM 12/5/2021 1:25:00 AM 12/5/2021 1:24:48 AM 12/5/2021 1:25:13 AM 12/5/2021 1:25:19 AM 12/5/2021 1:25:31 AM 12/5/2021 1:25:41 AM 12/5/2021 1:25:52 AM 12/5/2021 1:26:00 AM - Christine PotterJun 21, 2022Copper Contributor
MohdEzan Do a Search/Replace. Search 2021 and replace with 2022. You may want to highlight the column if you have other data that may be affected.