Unable to change date format in Excel

Wikitechy | 1980 Views | windows | 08 Jun 2016

 

Scenario:

Having the excel worksheet with a date column, the format is set to dd-mmm-yyyy but in general the dates in the columns display as mm/dd/yyyy. When we try to double click in each of the individual cell and then when we try to move out of that cell the format changes and the dates appear correctly. Considering, if we have 120516 dates then, we need to change, I can't take time to double click in each cell. 

Reason:

When we are trying to change date format in Excel, this problem occurs in excel worksheet.

Error:

Unable to change date format in Excel

Fix 1:

1. Go to run dialog, type regedit and then press the Ok button.

2. Click on HKEY_CURRENT_USER 

3. Select Control Panel option. (HKEY_CURRENT_USER ->Control Panel). 

4. Click on choose to International. 

5. Double click on sShortDate the another window will be open and then change the value of date. 


Fix 2:

1. Open your Excel spreadsheet and Right click on the top of the excel column and select Format Cells.

2. On selecting the Format Cells, we will get format cells window.

3. Over there select the Date option and select the format needed for this particular column or select it as text type. 

4. So that, our data will be populated as it is.


Applies to:

  • Microsoft Excel 2000(v9.0)
  • Microsoft Excel 2002(V10.0)
  • Microsoft Excel 2003(v11.0)
  • Microsoft Excel 2007(V12.0)
  • Microsoft Excel 2010(v14.0)
  • Microsoft Excel 2013(V15.0)
  • Microsoft Excel 2016(v16.0)

Related Errors:

  • Unable to Change Date Format in Excel
  • Excel Dates Won't Change Format 
  • Excel 2010 date formatting not working on some cells
  • How to change Excel date format and create custom formatting
  • In Excel 2010 why can't I change the date format?
  • Excel - Cannot Change Date Format
  • unable to change date format in excel 2010
  • unable to change date format in excel 2007
  • unable to change date format in excel 2013




Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise