Bmwolf21 Posted February 20, 2009 Share Posted February 20, 2009 I am entering data into a basic spreadsheet for work, with five fields: customer first/last name, DOB, phone # and T-shirt size. The DOB cells are formatted with a general date format, where the date entered as 4-5-30 defaults to 4/5/1930. But for some reason, a handful of birthdates are defaulting to a year 2000 date - the 4-5-30 is showing up as 4/5/2030. Any ideas what is causing the problem and how I might be able to fix it? Link to comment Share on other sites More sharing options...
shrader Posted February 20, 2009 Share Posted February 20, 2009 I am entering data into a basic spreadsheet for work, with five fields: customer first/last name, DOB, phone # and T-shirt size. The DOB cells are formatted with a general date format, where the date entered as 4-5-30 defaults to 4/5/1930. But for some reason, a handful of birthdates are defaulting to a year 2000 date - the 4-5-30 is showing up as 4/5/2030. Any ideas what is causing the problem and how I might be able to fix it? I don't know what the problem is, but try typing in the full 4-digit year. Link to comment Share on other sites More sharing options...
Glass To The Arson Posted February 20, 2009 Share Posted February 20, 2009 I wish I can help you but I'm work on a Linux It is a setting you can change... but for now - you might as well throw in the 4 digit year like Shrader said Select all the columns containing information, select Data / Sort and it will give you the option of which field to sort it by. Link to comment Share on other sites More sharing options...
Bmwolf21 Posted February 20, 2009 Author Share Posted February 20, 2009 I already formatted the cells so Excel would convert the 2-digit year into a 4-digit, but I don't know why most of the cells are defaulting to 19xx and a handful are defaulting to 20xx. When I finished the first sheet I ran a sort and there were only five cells that screwed up the date, so it was easy to correct the mistakes. I was just curious as to what might be causing it so I could avoid it/correct it in the future. Link to comment Share on other sites More sharing options...
meazza Posted February 20, 2009 Share Posted February 20, 2009 I already formatted the cells so Excel would convert the 2-digit year into a 4-digit, but I don't know why most of the cells are defaulting to 19xx and a handful are defaulting to 20xx. When I finished the first sheet I ran a sort and there were only five cells that screwed up the date, so it was easy to correct the mistakes. I was just curious as to what might be causing it so I could avoid it/correct it in the future. I remember I had the same trouble with Bloomberg Excel tools where I wanted to extract ex-dates and it would pull out some as mm/dd/yyyy and others as dd/mm/yy Try playing with the regional settings in the control panel. Link to comment Share on other sites More sharing options...
RayFinkle Posted February 20, 2009 Share Posted February 20, 2009 Have you tried turning off the computer and then turning it back on? That is my universal fix for everything. Link to comment Share on other sites More sharing options...
Just Jack Posted February 21, 2009 Share Posted February 21, 2009 Have you tried turning off the computer and then turning it back on? That is my universal fix for everything. And if that doesn't work, wipe and reload the hard drive. Link to comment Share on other sites More sharing options...
ieatcrayonz Posted February 21, 2009 Share Posted February 21, 2009 I already formatted the cells so Excel would convert the 2-digit year into a 4-digit, but I don't know why most of the cells are defaulting to 19xx and a handful are defaulting to 20xx. When I finished the first sheet I ran a sort and there were only five cells that screwed up the date, so it was easy to correct the mistakes. I was just curious as to what might be causing it so I could avoid it/correct it in the future. I have as much or more respect for old people than anyone, but let's face it, they look ridiculous in t-shirts. All of the flab and stuff shows through and it is disgusting. Excel is a pretty smart program. If you're trying to give a t-shirt to someone born in 1930 it is probably saying to itself :"no way am I giving a t-shirt to someone that old" and it adjusts. Change the t-shirt size to just the word size and it will allow you to default to 19xx. Link to comment Share on other sites More sharing options...
Ted Striker Posted February 21, 2009 Share Posted February 21, 2009 I am entering data into a basic spreadsheet for work, with five fields: customer first/last name, DOB, phone # and T-shirt size. The DOB cells are formatted with a general date format, where the date entered as 4-5-30 defaults to 4/5/1930. But for some reason, a handful of birthdates are defaulting to a year 2000 date - the 4-5-30 is showing up as 4/5/2030. Any ideas what is causing the problem and how I might be able to fix it? Surely, there must be something that you can do. Link to comment Share on other sites More sharing options...
Buftex Posted February 21, 2009 Share Posted February 21, 2009 I have as much or more respect for old people than anyone, but let's face it, they look ridiculous in t-shirts. All of the flab and stuff shows through and it is disgusting. Excel is a pretty smart program. If you're trying to give a t-shirt to someone born in 1930 it is probably saying to itself :"no way am I giving a t-shirt to someone that old" and it adjusts. Change the t-shirt size to just the word size and it will allow you to default to 19xx. Okay, sometimes your funny! Link to comment Share on other sites More sharing options...
BuffaloBill Posted February 21, 2009 Share Posted February 21, 2009 I am entering data into a basic spreadsheet for work, with five fields: customer first/last name, DOB, phone # and T-shirt size. The DOB cells are formatted with a general date format, where the date entered as 4-5-30 defaults to 4/5/1930. But for some reason, a handful of birthdates are defaulting to a year 2000 date - the 4-5-30 is showing up as 4/5/2030. Any ideas what is causing the problem and how I might be able to fix it? You are using a format that is *'ed i.e. *1/1/2009 switch the column to the format without an asterisk and your dates will go in as entered. With the asterisk your date formating is affected by the operating system and it will automatically enter anything in the range of 00 -29 as 2000 - 2029. seriously this is the fix. Link to comment Share on other sites More sharing options...
Bmwolf21 Posted February 21, 2009 Author Share Posted February 21, 2009 You are using a format that is *'ed i.e. *1/1/2009 switch the column to the format without an asterisk and your dates will go in as entered. With the asterisk your date formating is affected by the operating system and it will automatically enter anything in the range of 00 -29 as 2000 - 2029. seriously this is the fix. Thanks, I'll give that a shot. I still have another 75 or so entries to add tomorrow, so I'll see if that works. I can't change anything in the regional or date/time settings as someone suggested because it is a shared network computer and the IT department locked everyone out of those areas. Link to comment Share on other sites More sharing options...
Assquatch Posted February 21, 2009 Share Posted February 21, 2009 I am entering data into a basic spreadsheet for work, with five fields: customer first/last name, DOB, phone # and T-shirt size. The DOB cells are formatted with a general date format, where the date entered as 4-5-30 defaults to 4/5/1930. But for some reason, a handful of birthdates are defaulting to a year 2000 date - the 4-5-30 is showing up as 4/5/2030. Any ideas what is causing the problem and how I might be able to fix it? Excel is trying to tell you that you should be using Access for your database instead of a spreadsheet program. Link to comment Share on other sites More sharing options...
DCbillsfan Posted February 21, 2009 Share Posted February 21, 2009 You are using a format that is *'ed i.e. *1/1/2009 switch the column to the format without an asterisk and your dates will go in as entered. With the asterisk your date formating is affected by the operating system and it will automatically enter anything in the range of 00 -29 as 2000 - 2029. seriously this is the fix. You da man! Link to comment Share on other sites More sharing options...
Bmwolf21 Posted February 21, 2009 Author Share Posted February 21, 2009 Excel is trying to tell you that you should be using Access for your database instead of a spreadsheet program. Probably true. I'm probably lucky they even installed Word and Excel on that computer, let alone going with all those other crazy Office programs. Link to comment Share on other sites More sharing options...
Recommended Posts