Search : in
By :

Unable to change date format in Excel

Last answer on Oct 2, 2009 8:45:06 am BST rznbyz, on Sep 29, 2009 2:07:35 pm BST 
 Report this message to moderators

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

Configuration: Windows XP Internet Explorer 7.0,  MS Office Excel 2007

Best answers for « Unable to change date format in Excel » in :
Verifying date format in PHP ShowVerifying date format in PHP There are several ways to check the format of a date in PHP; the simplest method is to make use regular expressions. To confirm a date format DD/MM/YYYY, where the days and/or month can be given as...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
[Windows 7] Changing the format of your photos Show[Windows 7]Changing the format of your photos Changing the format of your photos or images, without downloading any software With Windows 7 it is possible to change the format of a photo with a few clicks. First go to your pictures and...
Ask your question Reply

1

venkat1926, on Sep 30, 2009 3:32:00 am BST

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

   
Reply to venkat1926

2

rznbyz, on Sep 30, 2009 11:38:58 am BST

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!

   
Reply to rznbyz

3

Justagal, on Oct 2, 2009 4:19:28 am BST
  • +2

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

   
Reply to Justagal

4

venkat1926, on Oct 2, 2009 5:24:22 am BST

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.

   
Reply to venkat1926

5

Justagal, on Oct 2, 2009 5:39:34 am BST

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

   
Reply to Justagal

6

venkat1926, on Oct 2, 2009 5:39:47 am BST

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

   
Reply to venkat1926

7

 venkat1926, on Oct 2, 2009 8:45:06 am BST

Thanks for the input

   
Reply to venkat1926
Ask your question Reply
They need your help