The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 10-15-2009, 04:38 AM
Northern Piper Northern Piper is offline
Charter Member
 
Join Date: Jun 1999
Location: Back in Riderville
Posts: 17,778
Why does Excel keep changing the format in my cells?

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?

Thanks.
Reply With Quote
Advertisements  
  #2  
Old 10-15-2009, 06:15 AM
NBYodo NBYodo is offline
Guest
 
Join Date: Nov 2005
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).

Should work.

NB
Reply With Quote
  #3  
Old 10-15-2009, 06:24 AM
sandra_nz sandra_nz is offline
Guest
 
Join Date: Jun 2005
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.
Reply With Quote
  #4  
Old 10-15-2009, 07:13 AM
Harmonious Discord Harmonious Discord is offline
Guest
 
Join Date: Apr 1999
Quote:
Originally Posted by sandra_nz View Post
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!'
That defines the input as text.

Last edited by Harmonious Discord; 10-15-2009 at 07:15 AM..
Reply With Quote
  #5  
Old 10-15-2009, 07:22 AM
D_Odds D_Odds is offline
Charter Member
 
Join Date: Nov 2001
Location: Queens
Posts: 10,182
Quote:
Originally Posted by NBYodo View Post
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).

Should work.

NB
Agreed. Once you know the codes, you can format dates any way you want, rather than Micro-Big Brother-soft formatting them for you.
Reply With Quote
  #6  
Old 10-15-2009, 02:10 PM
Northern Piper Northern Piper is offline
Charter Member
 
Join Date: Jun 1999
Location: Back in Riderville
Posts: 17,778
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?
Reply With Quote
  #7  
Old 10-15-2009, 02:21 PM
Giles Giles is offline
Charter Member
 
Join Date: Apr 2004
Location: Newcastle NSW
Posts: 12,009
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.
Reply With Quote
  #8  
Old 10-15-2009, 02:59 PM
Enginerd Enginerd is offline
Guest
 
Join Date: Jun 2000
Quote:
Originally Posted by Giles View Post
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".
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.
Reply With Quote
  #9  
Old 10-15-2009, 03:06 PM
Jack Batty Jack Batty is offline
Guest
 
Join Date: Jun 2000
Quote:
Originally Posted by Enginerd View Post
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
Reply With Quote
  #10  
Old 10-15-2009, 03:32 PM
Enginerd Enginerd is offline
Guest
 
Join Date: Jun 2000
Quote:
Originally Posted by Jack Batty View Post
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.
Reply With Quote
  #11  
Old 10-15-2009, 03:35 PM
Giles Giles is offline
Charter Member
 
Join Date: Apr 2004
Location: Newcastle NSW
Posts: 12,009
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.
Reply With Quote
  #12  
Old 10-15-2009, 04:28 PM
amarone amarone is offline
Charter Member
 
Join Date: Mar 2001
Location: Atlanta, GA
Posts: 4,520
Quote:
Originally Posted by sandra_nz View Post
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.
Yes you can. The main benefit of yyyy-mm-dd format is that it sorts correctly even when sorted alphabetically.
Reply With Quote
  #13  
Old 10-15-2009, 04:34 PM
D_Odds D_Odds is offline
Charter Member
 
Join Date: Nov 2001
Location: Queens
Posts: 10,182
Quote:
Originally Posted by amarone View Post
Yes you can. The main benefit of yyyy-mm-dd format is that it sorts correctly even when sorted alphabetically.
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.
Reply With Quote
  #14  
Old 10-15-2009, 07:11 PM
Cerowyn Cerowyn is offline
Guest
 
Join Date: Oct 2000
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.

Last edited by Cerowyn; 10-15-2009 at 07:13 PM.. Reason: Example added
Reply With Quote
  #15  
Old 10-15-2009, 08:22 PM
Northern Piper Northern Piper is offline
Charter Member
 
Join Date: Jun 1999
Location: Back in Riderville
Posts: 17,778
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.
Reply With Quote
  #16  
Old 10-16-2009, 02:19 PM
AllShookDown AllShookDown is offline
Guest
 
Join Date: Sep 2002
Quote:
Originally Posted by Enginerd View Post
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.
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.
Reply With Quote
  #17  
Old 10-16-2009, 02:41 PM
Disgscen Disgscen is offline
Guest
 
Join Date: Oct 2009
You're simply misunderstanding what MS means by the word 'General' here.

Quote:
"General format cells have no specific number format."
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.
Reply With Quote
  #18  
Old 10-16-2009, 03:50 PM
D_Odds D_Odds is offline
Charter Member
 
Join Date: Nov 2001
Location: Queens
Posts: 10,182
Quote:
Originally Posted by Giles View Post
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.
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.
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 07:30 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.