locked
Excel 2010 DEFAULT date format - How to set it RRS feed

  • 问题

  • 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.

    2013年10月19日 15:38

答案

  • 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.

    2013年10月20日 7:06

全部回复

  • 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.

    2013年10月20日 7:06
  • 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
    2014年5月2日 18:03
  • 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.


    • 已编辑 Uschi P 2015年3月26日 20:43
    2015年3月26日 20:40
  • 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!!

    2015年9月10日 20:41
  • I got around this by copying Sheet1, so the workbook has several sheets with the corrected date format applied.
    2016年3月30日 21:13
  • You also need to be a genius to apply this solution :-(

    Incredible how Microshaft never fixes things that people have been complaining about since the 1990's. What do they do there all day

    2018年1月23日 23:27