Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: microsoft date formats

04 Nov 2013   #1

windows 7 home premium x64
 
 
microsoft date formats

I like to have my regional settings as follows:
dd-mmm-yy (or yyyy), such as today: 04-nov-13 (this is because I live overseas, and constantly get confused about what I'm seeing between US/UK format).

however, I want to always input as US format (eg. 11/4 for the above example). in other words, I want US settings, but just only to tinker with display format.

my regional settings are "english (united states)", and short date "dd-MMM-yy" for example.
and in excel, the default date format is "*14-03-01".

how to accomplish this without having to change settings everytime I open a new worksheet?

thank you
-patrick

My System SpecsSystem Spec
.

04 Nov 2013   #2

Windows 7 Home Premium 64 bit sp1
 
 

The easiest way is to have the date format set for the US. As far as I know there is no multi date format available in Windows.
You could write a macro to set the date format the way you want for Excel but you would have to remember to run it every time you open a new worksheet.
My System SpecsSystem Spec
04 Nov 2013   #3

Microsoft Community Contributor Award Recipient

Windows 8.1 Pro with Media Center
 
 

As Ranger already mentioned, Excel takes the formatting info from Windows regional settings. However, you can modify the Excel number formatting (including date) and set it to differ from Windows regional settings simply with context menu > Format Cells option:
microsoft date formats-2013-11-04_120331.jpg
Kari


My System SpecsSystem Spec
.


04 Nov 2013   #4

windows 7 home premium x64
 
 

thank you
the problem is that both settings (windows and office/excel) appear to deal with the display format.
what I am troubled by is the input format...for example, I want march 14 to display as 14-mar, but to input/type as "3/14".
my regional settings are "english (united states)".
My System SpecsSystem Spec
05 Nov 2013   #5

Microsoft Community Contributor Award Recipient

Win 7 Home Premium 64bit Ver 6.1.7600 Build 7601 - SP1
 
 

Quote   Quote: Originally Posted by pson View Post
thank you
the problem is that both settings (windows and office/excel) appear to deal with the display format.
what I am troubled by is the input format
...for example, I want march 14 to display as 14-mar, but to input/type as "3/14".
my regional settings are "english (united states)".
Lets see if I understand you correctly,

When you select a cell and type in 3/14, you are expecting to see 14-mar as the result in that cell??
What version of Excel do you have??

This link explains Excel 2010, and expands on Kari's suggestion: Format numbers as dates or times | Office-Microsoft-Support; Click on Create a custom date or time format at "In this article" to see more.

Actually, the format you desire should be listed in the Format Cells function, it doesn't show in Kari's tutorial, maybe because the choices weren't scrolled down far enough, but it should be there, it is in the Support article.
If it isn't, make sure you are on the cell you want then go to Format Cells, click on User-Defined and type in dd-mmm. Make sure you use the hyphen, and not the slant! Then click on Apply and or OK.

I use Libre Office and that's what I had to do; my examples from Calc:
You can use either the Input line or enter directly into the cell:
Name:  pson1.JPG
Views: 18
Size:  19.8 KB

The result after touching the Enter key or moving off of the cell:
Name:  pson2.JPG
Views: 18
Size:  18.8 KB

It looks like what you are after should be set as: ddmmm or dd-mmm in user defined to give you 14-mar, but because March is a proper noun Windows and Libre treats it as such and you will see 14-Mar.

Make sure you save the workbook or you will lose the user-defined setting.

~~~ ~~~~ ~~~

If that still doesn't work for you, you may be able to do what you want with Windows PowerShell (How to Open)

Disclaimer:
I am not proficient with Windows Power Shell (WPS), but I do know enough to be considered "dangerous".

Following this Technet Blog: How Can I Create a Custom Date Format in Microsoft Excel? - TechNet Blogs it shows how to set specific dates in Excel with WPS.

The two lines highlighted in these WPS CMDs from "How can I create...." would be the ones you would "tinker" with.

Code:
Set objExcel = CreateObject("Excel.Application")  
objExcel.Visible = True    

objExcel.Workbooks.Add    

objExcel.Cells(1,1).Value = "01/01/2006"
objExcel.Cells(1,1).NumberFormat = "yyyymmdd"
warning   Warning
Before you begin I would hope you have backups to your OS and Apps, it is possible to corrupt either your OS or an app when using incorrect Power Shell CMDs. Some CMDs won't be recognized by Power Shell and it will tell you so just like a CMD prompt will so your okay there, but if you enter an incorrect one in the wrong place that it does recognize, oh-well.......

This would be a possible alternative while "tinkering"
Windows Virtual PC - Create Virtual Machine



More:
Scripting for Microsoft Office

Scripting with Windows PowerShell

Windows PowerShell Owner's Manual

Windows PowerShell Tip: Formatting Dates and Times

Windows PowerShell Tip: More Fun with Dates (and Times)


Apply Windows Power Shell to Excel Time and Date Formats | Google Search

Scripting for Microsoft Office | Google Search


My System SpecsSystem Spec
05 Nov 2013   #6

windows 7 home premium x64
 
 

thank you, anak
you are correct, I want to type "3/14" and see the date formatted as "14-mar-2014" ("14-Mar-14" less desirable, but also acceptable).
the problem seems to lie not in the date format, but in how excel recognises my input:
no matter what the date format in excel (even dd-mmm-yy), typing "3/14" always gives me "01-Mar-2014";
just as typing "3/12" (intended as march 12) causes "03-Dec-14" to be displayed.
excel is always interpreting the first number as the day (or month*) and the second as the month (or year, *if not logical).

that's where I'm stuck.
My System SpecsSystem Spec
05 Nov 2013   #7

Windows 7 Home Premium 64 bit sp1
 
 

You may have to use a date such as 3/14/14 so that you have all 3 there for Excel to translate you date situation. Just using 2 date numbers will probably always result in what is now happening to you. In other words Excel is executing your command correctly but it just does not have enough information to do what you want it to.
My System SpecsSystem Spec
05 Nov 2013   #8

Microsoft Community Contributor Award Recipient

Win 7 Home Premium 64bit Ver 6.1.7600 Build 7601 - SP1
 
 

Are you using the "User defined" option as described earlier?

3/14 giving you 01-Mar-2014, and 3/12 giving you 03-Dec-14, shows me you may have a corrupt Excel program because it is recognizing different aspects of the format.

If forcing the user defined option doesn't help, you may want to consider a repair of your Office Suite, with a possible SFC /scannow before the repair.
If the repair doesn't help you may need to re-install the Office Suite.

More graphical explanations:
Office 2010 - Perform Repairs

Repair Microsoft Office 2010 - Help Desk Geek

Less:
Repair-office-programs-office.microsoft.com

Edit:
I missed Ranger4's reply, that is another possibility, but not if you're using "user defined".
My System SpecsSystem Spec
Reply

 microsoft date formats




Thread Tools




Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 11:22 PM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33