date confusion

[mepetey]

general question here guys, I get a report that is in CSV form on a weekly
basis that inludes dates. Some of the data has the date format reversed.
i.e. 02/01 instead of 01/02.

I am ok with the logic of sorting this issue BUT, sometimes the data
transforms into a number. i.e. 49025. why should this be? how can I convert
it back to a "real" date.?

TIA any ideas

 

[brittonsm]

Are you doing anything other than opening the CSV in Excel? The
number i.e. 49025 is the date serial number - To convert back to a
date just change the cells format: Format->Cells (Ctrl-1)

Help?

 

[mepetey]

nope. just opening up the file. Thing is, some of the cells are ok. I
applied a TRIM and LEFT operator and in the affected cells i get a number.
In the others i get part of the date. I heap big confused...........



Are you doing anything other than opening the CSV in Excel? The
number i.e. 49025 is the date serial number - To convert back to a
date just change the cells format: Format->Cells (Ctrl-1)

Help?

 

[Dave Peterson]

First, I'm not sure I'd trust excel to open a .CSV file and give me the right
date for a string that was ambiguous--
02/01 could mean
February 1 of the current year.
January 2 of the current year.
February 2001 (no day)
2002 January (no day)

I'd rename the .csv to .txt and then open it via File|Open

Then I'd be able to see the text import wizard and I could specify how I want
that field treated (general, date (ymd, dmy, ...), text or ignore.

=====
If I enter 49025 in a cell and give it a date format, I get March 22, 2034 (with
1900 the base year).

What do you see in the text file when you look at that field on that line?

===
If the 49025 was just some numbers you posted in the message and you are seeing
some 5 digit number instead of all dates in the worksheet, then you may be
looking at formulas.

If you select the cell and look at the formulabar, do you see a real date?

If yes, try:
tools|Options|View tab|uncheck formulas
(xl2003 menu system)
(ctrl-` <ctrl-backquote to the left of the 1/! key on my USA keyboard> is the
shortcut toggle.)