Unable to change date format in Excel [Solved/Closed]
rznbyz - - Latest reply: Roprince - May 18, 2018 at 12:39 PM
Related:
- How to change date format in excel
- Date format not changing in excel - Best answers
- Excel date format not working - Best answers
- How to change date format in excel to dd/mm/yyyy - How-To - Excel
- How To Change the Date Format in Excel - How-To - Excel
- Change date format dd.mm.yyyy to dd/mm/yyyy ✓ - Forum - Excel
- Unable to change date format in excel ✓ - Forum - Excel
- How to change date format from yyyymmdd to mm/dd/yy in Excel ✓ - Forum - Excel
5 replies
Best answer
approved by Jean-François Pillou
on Nov 26, 2018
This works:
That works.
-24
- First change your format to the specific date that you want for your column of data.
- With the column selected, select "Copy"
- Open notepad (NOT WORD, it has to be notepad)
- Paste your data into notepad.
- In notepad, press CTRL + A (selects all cells) then press CTRL + C (copies data)
- Paste data back into Excel.
- It will say something about size not being the same, paste it anyways.
That works.
-24
Glad we were able to help! Love us? Write us a review! Rate CCM
4448 users have said thank you to us this month
View all 22 comments
Report
Thanks very much - works perfectly for large data sets.
Report
Respond to teddy
it worked.... :) thanku....
Report
Respond to SS
Thank you, this is a simple yet effective solution.
Report
Respond to Thaneesh
Thank u very much:)
Report
Respond to Roprince
Thank you so much
Hi Jack,
I can help you with this. Select the column/row that you need to reformat.
Go to the Data Tab
From the Data tab, Select Text to Columns
This will open a Wizard called "Covert Text to Columns"
You'll see 2 choices for your original data type: Delimited or Fixed Width
If your current date format has commas, tabs, dashes, etc., then click Delimited.
If your current date format has only spaces separating everything but no characters, then click Fixed.
Click NEXT once you selected your origiinal data type
The next screen, you can skip by clicking NEXT again
Now you'll see a screen that lets you slect the data format that you want
Select the DATE button
Then, from the drop down, select the new date format you want to use.
Click Finish
This should do the trick. I hope this helps!!
I can help you with this. Select the column/row that you need to reformat.
Go to the Data Tab
From the Data tab, Select Text to Columns
This will open a Wizard called "Covert Text to Columns"
You'll see 2 choices for your original data type: Delimited or Fixed Width
If your current date format has commas, tabs, dashes, etc., then click Delimited.
If your current date format has only spaces separating everything but no characters, then click Fixed.
Click NEXT once you selected your origiinal data type
The next screen, you can skip by clicking NEXT again
Now you'll see a screen that lets you slect the data format that you want
Select the DATE button
Then, from the drop down, select the new date format you want to use.
Click Finish
This should do the trick. I hope this helps!!
Not sure if any of these more complicated solutions work, but I saved it as a csv and it worked perfectly. Thanks!
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
the reason for this is as mentioned above - depending on the users settings - i.e. id it's US for example, whateer the user types in the cell will default to the mm/dd/yy (that's according to the default US format)
By clicking into the cell then clicking out, the cell recognises your settings - hence whay it reverts to the UK format
hope that makes sense
By clicking into the cell then clicking out, the cell recognises your settings - hence whay it reverts to the UK format
hope that makes sense