triadruid

Most businesses aren't tracking back that far, presumably.

From:

Okay, I tried it and it works for me. I'm using the Office 2003 version. Even made sure the cells were formatted as dates. Maybe you have an older version?

From:

Excel 2003, SP2. Not sure what's going on there, I've tried typing it in directly and deleting successive decades with a formula - it chokes right on schedule either way.

http://j-walk.com/ss/excel/usertips/tip028.htm has some documentation on it, and a work-around, it was just a funny coincidence...I ended up with an unformatable ######################## string when I tried to convert it to MM/DD/YYYY format.

From:

Are you using the 1904/Macintosh dating system, by chance? It apparently allows for negative dates (before the 0-date), whereas the default/Windows 1900 system chokes on it.

From:

Just checked under Tools>Options>Calculations and 1904 date system is unchecked. I'm on Windows XP here at work, the Mac is at home.

From:

See below, just to make sure I'm not crazy. It *looks* like it enters fine, but is actually getting input as text.

From:

How about giving me a function you'd like to perform and I'll see if I can recreate it.

From:

Here's one example:
=B1+36525 *should* give something in May, 1995.
Using the "=5/15/1895" formulation in B1, it says 12/31/1999. Using 5/15/1895, it gives #VALUE!, because it's interpreting it as text.

From:

(http://photobucket.com)

That's not what I'm getting. But then, I'm not sure I really understood what you want.

From:

Try this instead:

Enter a recent date on one line; 1970s, 1990s will do fine.
Next line, do =B1-5000; should subtract ~14 years or so.
Repeat that formula in a number of cells until you get into the 1800s.
What do you get on the next line?

From:

I was hoping it would work.

From:

Thanks for the help, in any case!

From:

diermuid.livejournal.com

I tried it using 5 different date formats, works fine for me. All I can think of is that your column might need to be widened.

Running 2003 SP2 here.

From:

I tried auto-widening the column by clicking on the header border, but it didn't change much. I suppose I didn't try widening more than 3", but that seemed extreme for any sort of date format...

I tried it in a brand new column of that width, and got 1/0/1900 when I tried "=5/15/1895". 5/15/1895 by itself gets interpreted as text, so you can't do any date/math functions on it.

From:

diermuid.livejournal.com

I had changed the cell to a date format, then added the date in it.

From:

Interesting. See my recursive example above with

tjstein. I think there was some confusion about my original difficulty. (I can get it to *display* 5/15/1885 just fine, it just doesn't consider it a date/number).

From:

diermuid.livejournal.com

I noticed that, when I do the equals then it goes all 1/0/1900 on me. Whooda thunk that Excel 2003 is not Y1.9k compliant?

From:

Also, yeah, mine works. Excel 2000.

From:

Time for ~~a beer~~ lunch, clearly.

I need a whisky icon...

From:

You and me both (on both counts).

From:

Interesting. See my recursive example above with

tjstein. I think there was some confusion about my original difficulty. (I can get it to *display* 5/15/1885 just fine, it just doesn't consider it a date/number).

From:

Eeeenteresting. When I first tried it, I entered the date and it displayed it, but was displaying it as text. I changed the format to date, and it did not choke on that, changed the date to several different formats, that seemed fine. When I did the calculation as you have there, it did indeed choke and return a string of ########.

From:

Whoa. I'm not crazy.

All is right with the world...or something.

From:

Using the behavior of Microsoft products to determine your sanity is so, so not recommended.

Glad you're feeling more stable though. I know you like that sort of thing.

From:

florilegia.livejournal.com

Have been to Carhenge. Where is Strawhenge?

From: