Excel being "not" so helpful with dates

Posted by Mark Wight on May 17, 2001 7:21 AM

Good morning ;)

Quick question, I am attempting to import a large ammount of data into an excel spreadsheet. The import goes well, the data goes in well... except for one small problem, some of my data in one of the cells looks like "25-1" thats all fine, however excel tries to be super helpful and format it to 25-Jan. I attempt to convert it to general, thinking I outsmarted the program. Again, excel tries to be helpful and it gives me the minutes since that date or something.

I try to repunch the value and it does the same thing. Attempt to re-adjust formatting before I leave the cell. I couldn't find anything in the format -> autoformat or any such area. Am quite bamboozled as to the problem.

Just wondering if anyone would have the slightest clue as to what my problem might be or how I would fix it.

Thx in advance =)

Posted by Mark W. on May 17, 2001 7:32 AM

How are you "importing"?

Posted by Mark Wight on May 17, 2001 7:47 AM

&LT;snip!>

Unsure of exactly how the data is imported, I don't work on the database, just the peon on this section of the chain, my VMS knowledge kinda sucks and I don't have a clue about how that scary database works. But the interface is able to import properly to an excel spreadsheet. The rest of the data comes out fine and picture perfect.

As well, if I attempt to manually enter in the date by hand and tool around with getting it to a general number format. It goes from 25-1 when I type it in by hand and changes to 25-Jan automatically, the formula box says 2001/01/25.

I format the attributes of the cell to General, it gives me back 36916. I attempt to format the cell when I type and it only allows me to change font properties not cell properties.

Posted by Mark W. on May 17, 2001 8:06 AM

I'm sorry to say that I can't be of much help
unless I know more about your data import. I
suspect that it's controlled by a macro which
when parsing the incoming data it's assigning
a DMY format to that value.

Posted by Mark Wight on May 17, 2001 8:12 AM

[snip!]

It has nothing to do with the importing process, pop open a spreadsheet, type "25-1" into any cell, hit enter, click, whatever and it changes to 25-Jan.

There are no macros present anywhere in this spreadsheet, nothing is in the cell formula box(or whatever it is called) and the vb editor has nothing in any modules or sheets.

I have tried this in both the sheet with the imported data, and a brand spankin new sheet, even went so far as to go on another computer that hasn't been involved and same thing happened.

Posted by Mark Wight on May 17, 2001 8:25 AM

Re: Excel being

Erh... that one didn't sound too nice ;)

As well, meant popped open a new workbook, sheet in same workbook on the computer would be under the influence of the same macros.

From chatting with the lady all she knows is that the company who designed our database, supplied us with an interface which imports ALL tags to excel individually in a string.

So a typical tag would be something like

number_mixed_text_text

Its imported into cells breaking it apart at the "_" between each text block.

Rinse and repeat for 30k or so records.

If you are unable to help don't worry ;) Already harassed IS, and the Helpdesk "Uhhh... lemme get my excel expert to call you back" they never called back and that expert always seems to be gone to the washroom =)

Posted by Mark W. on May 17, 2001 8:43 AM

The thing is... when I enter 25-1 into a cell it
remains as "25-1". Now if I enter 1-25 it converts
to a date. I assume that you're on Windows NT.
By chance are your Regional Settings set to
"English (United Kingdom)". If so change it
back to "English (United States)".

Posted by Mark W. on May 17, 2001 8:44 AM

See my posting...

17677.html

Posted by Mark Wight on May 17, 2001 8:49 AM

Oi..

Nope, regional settings are set for English (United States)

Date format would be yyyy/MM/dd

So this further adds to the confusion =\

Thx for the suggestion tho.

Posted by Mark Wight on May 17, 2001 8:53 AM

Re: Oi..

Just for humor, I attempted to use conditional formatting. Well, 25-1 manually typed into the box lands me the same 36k value =\

Heh, I love excel ;)

Posted by Mark W. on May 17, 2001 9:48 AM

Well, there you have it!

yy/mm/dd format causes 25-1 to be translated by
Excel to 25-Jan. That's not the case for m/d/yy.

Posted by Mark Wight on May 17, 2001 10:19 AM

Re: Well, there you have it!

w00t!!!1

Thanks alot for you help =)

btw, nice looking name ;)

Posted by Ric hard S on May 17, 2001 11:10 PM

Try text format on import

I've had similar probles in Australia, except I had numbers 1-25 being converted to Jan 25. When I imported, I designated that column as text. Seemd to work.