date format is impossible to change
When typing in a date such as this: "7/15" it is impossible to make this value show up in a new worksheet as 7/15/currentYear in a format I want. It will always show up as "15-jul" or "dd-mmm". Yes, the date will be correct, it will also be correct for the USA, but it's not in a visual format that I like and I have to manually change the cell format because there's no universal way to make this setting.
This is already supported in Excel today. Right click the cell, select Format Cells > Number tab > Date, and then choose the date format you’d like.
Hope that helps,
8 commentsComments are closed
I am using a spreadsheet designed to look like the tax form 1040. It looks fine when I first open it and enter data. Upon reopening after saving, all of the cells acting as line numbers (they just contain a simple integer number) revert to date format and display #####. The spreadsheet is protected by password by the creator. So, the general suggestions of manually changing the format are useless. I essentially cannot use the sheet any longer because it prints the ####.
Ahh, I misunderstood, thanks for the clarification.
Excel picks up it's default formatting from the system, so if you change the default formatting for dates in Windows, Excel should use those as well.
This site (and there are likely others) has a good description of how to change your system settings: http://www.sysprobs.com/change-date-format-windows-7-ddmmyyyy
Hope that helps,
M clark commented
Like I said, "It's impossible to make this value show up in a new worksheet". I have to make changes to it like you said. My point is that I don't want to make this change every time I make a new worksheet. I would like for there to be somewhere in the system that I could make this change so I would not have to manually change it every time.
I would also like to be able to default the date format for all new sheets instead of having to format the date each time I start a new sheet.
I have to agree with "Anonymous". In previous versions of Excel (back when it was a usable product), you could change the default date format globally, so you didn't have to change it in each worksheet! Thanks Microsoft.
This drives me up the wall. Why would one want to only show part of a date? Please make the entry selectable as you do font and font size
Right click on cell >>go to format cells>> go to number tab>> chose 'custom' on left > type in "dd-mmm" in the 'Type:' on the righter side and hit 'ok' and you are done!! just paint the format for other cells you want with similar format.
Kenneth Barber commented
Right click the cell, Format Cells, Number tab, Date. Choose your date format there.