I've read the posts and no suggestion (note that I said "suggestion", not "solution") worked. Why is the date format set to d-mmm anyway? Who uses that?

How do we set default settings for ALL worksheets and workbooks? Is there a template that we can modify?

Setting the Control Panel Date format DOES NOT work. Excel does not use that.

Setting to use 1904 format DOES NOT work.

Setting the -click on Spreadsheet TAB... View Code... Enter this... Does NOT work for future workbooks. It is for ONLY THAT SHEET and not the entire workbook anyway.

And, please do NOT reply with how to set it for the current worksheet - I know how to do that.

Thank you.

Hello,

Please this process step by step and you will have what you want.

1. create a new excel file and save it to this folder name is date-formatted C:\Users\Username\Application Data\Microsoft\Excel\XLSTART (change username as per your PC)

2. open the file which you just created date-formatted

3. now right click on any opened Worksheet Tab and click View Code

Paste the following code in worksheet code module

right click on the worksheet tab, select "View Code" to bring up the VBA editor.

Paste this code into the worksheet module code window that is displayed:

 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.NumberFormat = "d-mmm" Then
        Target.NumberFormat = "dd/mm/yyyy"
    End If

End Sub

change above date formats as you need.

save everything and close excel.

Now every time you create a new workbook it will have the desired date format you want.

only step you were missing is creating a new file with pre-defined formats you want and place it in the folder of office 2010 installation folder so it opens everytime you open excel.

This will solve your problem.

Please mark as answered or helpful to keep the forum tidy and organized.

• Marked as answer by Tony Chen CHNMicrosoft contingent staff, Moderator Monday, November 04, 2013 2:15 AM

You are a GENIUS!  Nowhere else have I found a solution for Excel's default date format issue--all the numerous posts I have found end with it just can't be done.  Thank you so much for this SOLUTION to setting the default Excel date format!  ~Pam

I would love to get more help on this. I saved the newly created excel file Book1.xlsx. Right clicked to view code and pasted the code.

Once I hit save, I get the following error message:

The followling features cannot be saved in macro-free workbooks:

• VB project

To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes.

If I click yes it does not seem to work and if I click no and save it as the mentioned file type it does not seem to work either. Would you be able to guide me through the rest of the steps? This automatic date thing is driving me crazy.

I am using Windows 7 and Excel 2010

Another thing the only way I could find the Exel/XLSTART folder was under Roaming, if that makes a difference.

• Edited by Uschi P 10 hours 45 minutes ago

I would love to get more help on this. I saved the newly created excel file Book1.xlsx. Right clicked to view code and pasted the code.

Once I hit save, I get the following error message:

The followling features cannot be saved in macro-free workbooks:

• VB project

To save a file with these features, click No, and then choose a macro-enabled file type in the File Type list.

To continue saving as a macro-free workbook, click Yes.

If I click yes it does not seem to work and if I click no and save it as the mentioned file type it does not seem to work either. Would you be able to guide me through the rest of the steps? This automatic date thing is driving me crazy.

I am using Windows 7 and Excel 2010

Another thing the only way I could find the Exel/XLSTART folder was under Roaming, if that makes a difference.

• Edited by Uschi P Thursday, March 26, 2015 8:43 PM

Has anyone come up with a solution that doesn't involve creating a macro-enabled worksheet? Also, the solution doesn't work once you add another worksheet.

There has to be a way to change the default formatting.  This is nuts!!