microsoft date formats

[pson]

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

 

[Ranger4]

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.

 

[Kari]

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:

​

Kari

 

[pson]

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)".

 

[Anak]

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:


The result after touching the Enter key or moving off of the cell:


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.

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

objExcel.Workbooks.Add    [B][COLOR=Blue]

objExcel.Cells(1,1).Value = "01/01/2006"[/COLOR]
[/B][COLOR=Navy][B]objExcel.Cells(1,1).NumberFormat = "yyyymmdd"[/B][/COLOR]

   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.......:shock:

This would be a possible alternative while "tinkering"
http://www.sevenforums.com/tutorials/8254-windows-virtual-pc-create-virtual-machine.html

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

 

[pson]

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.

 

[Ranger4]

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.

 

[Anak]

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:
http://www.sevenforums.com/tutorials/110909-office-2010-perform-repairs.html

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