I’m doing a table in Excel that involves some dates. I want the dates to be in the YYYY-MM-DD format. Since there isn’t that option in the “Dates” format for a cell, I’ve set the format to “General”. That worked well, until about 10 entries in the table, when Excel suddenly started to re-format the entry to dd/mm/yy.
No matter how many times I highlight the cell, go to Format -> Cells and click “General”, Excel won’t accept the change. It insists I must use dd/mm/yy.
Any thoughts on how I can get it to use the “General” format for the cell?
I don’t know specifically how to get Excel to display your date as yyyy-mm-dd format by using the General formatting, but you could do it this way instead:
choose Custom formatting, and enter YYYY-MM-DD as the format (I assume you’re using an English version of Excel).
Yep, custom formatting is the way to go. The other option is to put an apostrophe before your date, i.e. '2008-10-15, which is a way of telling Excel ‘don’t touch this! i like it just how it is!’
The problem with that solution, though, is that Excel won’t know they’re dates, so you won’t be able to do anything like sort your table in date order.
thanks for the suggestions, everyone. I’ll try them.
But why does Excel not allow me to change the cell format to “General”? why did it let me use “General” for part of the table, then unilaterally switch the format on me?
The problem with “General” is that if Excel thinks it recognises the format of what you’ve entered, it changes the cell to that format. So, e.g., if you have 4-digit part numbers, and you put in a part number like “0123”, Excel thinks this is a number, and treats it as a number, displaying as “123”.
To get around the problem, you must define the cell format as “Text”. Then Excel will leave alone (almost) anything that you put in the cell, and display the same way as input. The problem may be that it will then sort as text, and that may not be exactly what you want. However, dates input in the YYYY-MM-DD format will sort correctly – though they will not sort correctly if the data that you are sorting includes some cells in Excel’s date format.
You don’t have to use text for this application - go to “custom format” and enter “0000” in the format box. Excel will give you the four digit number with a leading zero, and you’ll still be able to use that data in mathematical operations.
But you might run into problems if you are using that cell in a lookup formula. Even witht 0000 format, you’d see the leading zero, but the value would still be just 123. =VLOOKUP(“0123”, range, column, FALSE) would return #N/A
Only if you use the lookup with a text argument. If you format it as a number and use the number as the lookup argument, Excel treats it the same as 123 except for the display.
The other reason why you might want “0123” treated as text is if other data in the same column had the format “0123A”. If “0123” is treated as a number and “0123A” is treated as text, then they won’t sort next to each other, as you might expect and want.
True, which is why I use that on documents where I need to maintain version control. However, if you are doing any date calculations in excel, it won’t understand the item as a date.
If the locale of your machine, and therefore by default, Excel, is Canadian, then dd-mm-yy is the General format for a date. It is putting it in the format you’ve told it to. You can change Excel’s default date display by changing the locale settings for Windows, or by setting the cells’ format to the custom string described above.
I note that Excel 2007 does have that date format for cells, and just about every other variation of formatting that you can imagine (yyyy-mm-dd is the sixth option on my installation). I thought earlier versions had the same option as well, but I’ll defer to the OP’s experience since I no longer have any machines with an earlier version on them.
That’s not what my copy of Excel says. It says that “General format cells have no specific number format.” So when I wanted to put the date in yyyy-mm-dd, I picked that one, because I thought it wouldn’t try to force me to use any particular format. But it does - it changes the format for the cell to “Date”, and will not let me change it back to “General.” Nor is yyyy-mm-dd an option for the “Date” format on my copy of Excel (Office 2004 for Mac).
As usual, Bill Gates’ programs decide for themselves what I should be doing, and correct me when I tell them I don’t want to do that. They really should call their applications Hal-Excel.
Thank you so much for this tip. My co-workers insist on outputting txt files from SAS instead of less trouble to open csv files because of the leading 0 issue. I refuse to go to the trouble most of the time. Now I have a way to display them when I absolutely have to w/o dealing with txt files.
You’re simply misunderstanding what MS means by the word ‘General’ here.
This is true, but this doesn’t meant there’s no format–just no “specific” format. In fact, General is the format you get in all cells in a new workbook. It doesn’t mean that Excel won’t try to figure out what the data is–in fact, if you leave a cell General, you’re telling Excel to try to figure out what type of data you’ve entered, and to format it as such. If Excel didn’t do this, you’d have to manually choose the format of everything you enter.
If you want a cell to have no number format at all, use the Text format, as mentioned previously. Text does what you felt General should have.
For what you want–a date formatted one exact way, which Excel doesn’t provide out of the box, create a custom format as explained in post #2, and recommended at least twice thereafter.
This is a big PITA for those in finance who work with cusips. Some cusips are all digits and have leading zeroes. Some cusips have the letter “E”. If you don’t format the column to text before downloading the items, the leading zeroes are lost, and the cells with “E” think you mean scientific format and there is no going back.
All because, as Northern Piper noted, Bill Gates is smarter than you and knows what you want.
