Unable to change date format in Excel

Solved/Closed

Excel

rznbyz - Sep 29, 2009 at 09:07 AM
Tobby - Sep 6, 2019 at 08:43 AM

Hello,
I have an excel worksheet with a date column, the format is set to dd-mmm-yyyy but the dates in the columns display as mm/dd/yyyy. If I double click in each individual cell and then move out of that cell the format changes and the dates appear correct. I have 24854 dates I need to change, I can't take time to double click in each cell. Any suggestions? Thanks

Cannot change date format in excel
Unable to change the date format in excel - Best answers
Excel cannot change date format - Best answers
Unable to change date format in excel ✓ - Forum - Excel
Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy - Guide
Cannot change the date format in Excel 2007 ✓ - Forum - Excel
Changing format of date in Excel ✓ - Forum - Excel
Unable to Convert Date in Excel ✓ - Forum - Excel

5 replies

Answer 1 / 5

24
Updated on Nov 26, 2018 at 11:27 AM

This works:

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

nik
Jun 11, 2010 at 08:13 AM

Thanks a lot man

noone
Sep 8, 2010 at 11:36 AM

this is the best answer, thanks

Harshi
Oct 28, 2010 at 05:57 AM

Thank u!!!

Dude Here
Nov 18, 2010 at 03:17 PM

sweet work bro

vishwas
Nov 25, 2010 at 03:28 AM

i have change in formula bar cell format of date in excel 2003

Answer 2 / 5

Nazie
Jan 13, 2010 at 11:46 AM

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!!

jack
Jan 13, 2010 at 12:49 AM

Hi,

this still doesnt help I tried. please help. or am I doing it wrong?

holly > jack
Mar 24, 2010 at 04:23 PM

Worked for me too. thanks

miked > jack
Mar 25, 2010 at 09:00 AM

Thanks for the help ... spent too much time trying to figure this one out.

CJ > jack
Mar 26, 2010 at 01:22 PM

Not sure if any of these more complicated solutions work, but I saved it as a csv and it worked perfectly. Thanks!

KIDLARK > jack
Apr 5, 2010 at 05:09 PM

Text to columns worked for me too! (I had already formatted the cells first)

Answer 4 / 5

venkat1926

Posts: 1864
Registration date: Sunday June 14, 2009
Status: Contributor
Last seen: August 7, 2021

810
Sep 29, 2009 at 10:32 PM

how did you format the cell

ok suppose tje relevant date is 1 sept 2009
you enter (in my excel you have to enter as m/d/yy)
9/1/09
now go to format-number-custom
and type at the top window
dd-mm-yyyy
and sees what happens

rznbyz
Sep 30, 2009 at 06:38 AM

Nothing happens....that is the problem. However, if I double click inside one of the date cells, after I have changed the format to the custom format, the date format changes to the correct format. But I can't go through the worksheet and double click in each cell...that would take a week!

Neelu
Oct 29, 2010 at 06:21 AM

i have a date format in mm/dd/yyyy but I want to convert into dd/mm/yyyy in Ms-excel 2003. if any body know then plz tell me.

Similar discussions

How to change date format from yyyymmdd to mm/dd/yy in Excel?

paste data into alternate blank cells

Convert number to spelling

Counting number of times a value appears if..

How to calculate positions(1st,2nd,3rd...) in Excel

Removing " false" value in "IF"

Similar discussions

Subscribe To Our Newsletter!