I have two different spreadsheets with data entered by two different users, one has used the date format 00.00.00 and the other has used 00/00/0000. I've copied and pasted all of the info from one sheet into the other to combine them to make just one copy - they look the same except for the two date formats. As there's hundreds of them I obviously don't want to go through and amend every single entry - how can I do this automatically? Switching the Format Cells > Date doesn't work for this.
Thanks!
Changing mass data's date format
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Changing mass data's date format
Try the following; I'd test it on a copy of the worksheet first.
Select a column with dates.
On the Data tab of the ribbon, click Text to Columns.
Click Next > twice, so that you are on Step 3 of the wizard.
Select Date, and select DMY from the dropdown next to it.
Click Finish.
If the result is not what you wanted/expected, you can undo the change by pressing Ctrl+Z.
Select a column with dates.
On the Data tab of the ribbon, click Text to Columns.
Click Next > twice, so that you are on Step 3 of the wizard.
Select Date, and select DMY from the dropdown next to it.
Click Finish.
If the result is not what you wanted/expected, you can undo the change by pressing Ctrl+Z.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 130
- Joined: 12 Aug 2020, 08:40
Re: Changing mass data's date format
Works like magic - thank you v much, would have never worked that out on my own! :)