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

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?

On Dec 21, 11:33*am, "mepetey" <peter.terr...@tiscali.co.uk> wrote:
> 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

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...........



"brittonsm" <(E-Mail Removed)> wrote in message
news:3fcbdf79-919c-4462-a1ab-(E-Mail Removed)...
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?

On Dec 21, 11:33 am, "mepetey" <peter.terr...@tiscali.co.uk> wrote:
> 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

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.)

mepetey wrote:
>
> 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...........
>
> "brittonsm" <(E-Mail Removed)> wrote in message
> news:3fcbdf79-919c-4462-a1ab-(E-Mail Removed)...
> 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?
>
> On Dec 21, 11:33 am, "mepetey" <peter.terr...@tiscali.co.uk> wrote:
> > 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

--

Dave Peterson