i have a very simple spreadsheet with these columns: name of a municipality, website, date last checked, notes. i selected the date last checked column & went to format cells. selected Date > 3/14/01, hit ok. now when i type in last friday's date, it changes it to 1/14/67. i can't find anything on google to fix this. if i type a different date, say march 14 2001, it changes it to 12/30/85.

WTF

please halp

6/13/2011 9:31:00 AM

i think this is a result of what excel starts counting dates from.

6/13/2011 9:49:59 AM

ideas on how to fix it?

6/13/2011 10:03:45 AM

I have pressed F1 in Excel, typed in "wrong date," and am currently learning more about your issue.

6/13/2011 10:34:47 AM

http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=excel+messing+up+dates

really not trying to be a dick about it, but this is a common problem and this is more efficient of reproducing a response here.

[Edited on June 13, 2011 at 10:44 AM. Reason : .]

6/13/2011 10:39:25 AM

^^i just did that & so far haven't found anything that fixes it.

Quote :

"If you type a date in a cell that has a Number data type, Excel will display its numeric form instead of the date. Keep in mind that Excel stores dates as numbers, beginning with Jan 1, 1900 as 1. Each successive day increases by 1, so May 31, 2011 is 40694. Changing the data type to General won't help in this case. Instead, just choose a Date format."

that may somehow be relevant, but i started this with it already in date format.

^i'm currently reading through those & none of them is my problem.

it's not the 1900 vs 1904 issue. there is a MUCH larger time gap between the date i type & the date it changes to than the 4 years + 1 day thing. it's not displaying as the formula for the date either. i type 6/10/2011 & it changes it to 1/14/2067, or 03/14/2011 to 12/30/1985.

[Edited on June 13, 2011 at 11:04 AM. Reason : ]

6/13/2011 10:44:57 AM

screw it. i set it to text & i'll just type them in.

6/13/2011 11:16:29 AM

If you want better help, you should specify the version of Excel you're using and the file format of your worksheet. Ideally, you'll post a sample record in raw text.

Change the format of your column with 6/10/2011 to Number. What is the number?

[Edited on June 13, 2011 at 11:29 AM. Reason : ...]

6/13/2011 11:28:02 AM

k so now it's not autoformatting anything anymore, regardless of what i set it to or type apparently. i added a column for phone numbers & it won't do that one correctly either. at least it doesn't change the number there.

it won't even format the dates at all anymore (so it's not making those same changes anymore). ugh. something screwy is going on here.

excel 2010, .xlsx

i copied a portion of the sheet & pasted it (plain text) into a new sheet, then tried to format columns again. it's still doing the same thing with the dates, but the phone numbers are working fine. in each of these date columns the result i want is 6/10/11 but this is what it gives me.
and here's some of the text. this probably won't show up very well. i added the dots here to try to make it easier to read.

Government....Website.....Date last checked.....Notes.....Manager.....Phone number
Northampton County.....http://www.northamptonnc.com/minutes.asp.....1/14/67.....doesn't work w/ FF.....Wayne Jenkins.....(252) 534-2501
Fayetteville.....http://www.cityoffayetteville.org/ccmeetingminutes.aspx.....1/14/67..... .....Dale Iman.....(910) 433-1329
Greene County.....http://www.co.greene.nc.us/agendasminutes.aspx.....1/14/67..... .....Don Davenport.....(252) 747-3446

[Edited on June 13, 2011 at 11:49 AM. Reason : read]

6/13/2011 11:37:53 AM

Format the Date Last Checked column as a number and post the numbers that correspond to the dates. That way, we can confirm which date system it's using.

[Edited on June 13, 2011 at 11:50 AM. Reason : >..]

6/13/2011 11:50:02 AM

61011.00

6/13/2011 11:59:39 AM

are you typing in the dates as 6/10/11

or 61011

because that looks like your problem

31411 = 12/30/1985 as well



[Edited on June 13, 2011 at 12:04 PM. Reason : lkj]

6/13/2011 12:01:29 PM

i'm actually typing 061011 & selected 06/10/11 as how i want it to show up. i've done this before tons of times.

i haven't done this since switching to 2010 though. maybe that is the hiccup.

god damn it now it won't center. i'm gonna use google docs now.

[Edited on June 13, 2011 at 12:07 PM. Reason : ]

6/13/2011 12:04:18 PM

061011 is day number 061011

[Edited on June 13, 2011 at 12:08 PM. Reason : from wherever excel starts counting]

6/13/2011 12:08:26 PM