Ask a question Report

Unable to change date format in Excel [Solved/Closed]

rznbyz - Latest answer on Oct 12, 2012 04:16AM

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

How to change date format in excel
Change date format in excel
Excel date format
Cannot change the date format in Excel 2007 (Solved) » Forum - Excel
Excel - Unable to change date format » How-To - Excel
DATE FORMAT IN EXCEL-2007 (Solved) » Forum - Excel
I want to change date format in Excel (Solved) » Forum - Excel
Excel date format won't change (Solved) » Forum - Excel

Answer

+144

24 May 18, 2010 12:46PM

This works:

1) First change your format to the specific date that you want for your column of data.
2) With the column selected, select "Copy"
3) Open notepad (NOT WORD, it has to be notepad)
4) Paste your data into notepad.
5) In notepad, press CTRL + A (selects all cells) then press CTRL + C (copies data)
6) Paste data back into Excel.
7) It will say something about size not being the same, paste it anyways.

That works.

-24

nik - Jun 11, 2010 08:13AM

Thanks a lot man

noone - Sep 8, 2010 11:36AM

this is the best answer, thanks

Harshi - Oct 28, 2010 05:57AM

Thank u!!!

Dude Here - Nov 18, 2010 03:17PM

sweet work bro

vishwas - Nov 25, 2010 03:28AM

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

miley - Dec 6, 2010 08:53AM

I was having the same problem and this solution worked a treat thanks

AJN - Oct 9, 2012 04:01AM

Thank you thank you thank you. Been looking for a cheat/shortcut on this one for a long time.

Golu - Oct 12, 2012 04:16AM

Does not work for me? :(

Answer

+23

Justagal 1Posts Thursday October 1, 2009Registration date Oct 2, 2009 12:39AM

Actually I find the problem:
Go to:
Registry
HKEY_CURRENT_USER
Control Panel
International
Change the sShortDate

Answer

+18

Michelle Dec 7, 2010 07:00PM

This is a problem I have had for several months and TEXT to COLUMNS is the answer!! Thank you, thank you!

Answer

+10

Justagal Oct 1, 2009 11:19PM

I have the same problem.. I find out that is not inexcel that you need to changethe date format is in the Config sys. If you go to the command prompt and type date it will give you the DD/MM/YY which is the format that is used in latin countries and canada... How to change that? I have no idea... Maybe someone can tell us here.. Thanks

Answer

venkat1926 1487Posts Sunday June 14, 2009Registration date ContributorStatus June 17, 2013Last seen Sep 29, 2009 10:32PM

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 06:38AM

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 06:21AM

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.

Answer

jack Jan 13, 2010 12:49AM

Hi,

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

Nazie - Jan 13, 2010 11:46AM

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

Sandipa - Jan 18, 2010 02:24AM

@Nazie

Thanks Nazie... It worked for me..

suresh › Nazie - Jan 30, 2010 01:31AM

Very 2 thanks for the same I also faced the same problem but resolved by your solution in this blog .

holly - Mar 24, 2010 04:23PM

Worked for me too. thanks

miked - Mar 25, 2010 09:00AM

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

CJ - Mar 26, 2010 01:22PM

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

KIDLARK - Apr 5, 2010 05:09PM

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

mossy - May 6, 2010 02:28AM

Gold star Nazie

Answer

Mak Jan 18, 2010 11:09AM

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

Answer

venkat1926 1487Posts Sunday June 14, 2009Registration date ContributorStatus June 17, 2013Last seen Oct 2, 2009 03:45AM

Thanks for the input

BooBoo - Jan 6, 2010 08:46AM

Why would double clicking the cell make the format change take place, i am still having this problem. In Excel 2003 you could do some tricks with coping and paste special values, but that does not work in Excel 2007.

Mike - Jan 11, 2010 06:27PM

If you save your file as csv and change the format it will work. Its just a jacked up excel file.

Answer

venkat1926 1487Posts Sunday June 14, 2009Registration date ContributorStatus June 17, 2013Last seen Oct 2, 2009 12:24AM

Im guessing. this may something to do with regional settings. Regional settings is control panel. This appears to be operating systems pronblem

place the problem in
PC review forums whose url is

http://www.pcreview.co.uk/forums/

choose the relevant newsgroup for your operating system.

Answer

venkat1926 1487Posts Sunday June 14, 2009Registration date ContributorStatus June 17, 2013Last seen Oct 2, 2009 12:39AM

sorry one more guess work

is the cell in which date is entered originally formatted as "text" . please check this

you better format all the cells as "general" and then enter the date and format it and see

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Unable to change date format in Excel [Solved/Closed]

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!