Imported file Date formats unrecognisable/unconvertable

[Mike Valente]

I have decided to try and seek help rather than pulling any more hair out


I have a text file which I am importing successfully into Xcel 2003. (Win XP
Pro Svce Pack 2 & updates)

There are a number of date fields in this file in the format yyyymmdd. ie.
20051013
This is a format that Xcel does not recognise.
To produce a regognised date format I am using the formula
=MID(N2,7,2)&"/"&MID(N2,5,2)&"/"&MID(N2,1,4) where n2 is the cell containing
the imported date field, this produces a dd/mm/yyyy format for the date
field in a different target cell. ie. 13/10/2005
When I try and FORMAT CELLS, Date, another date format, nothing happens.
This is true if I copy and then paste values only or leave as a formula.
From this I interprit that the cell contents is not recognised by Xcel as a
date.
If, however I type in a date using the format dd/mm/yyyy, identical to what
is produced by the formula, Xcel immediately recognises this as a date and
allows the format to be changed with the FORMAT CELLS, Date etc.
Thus I am led to conclude that as the product of a formula the number,
although in dd/mm/yyyy format will not be recognised as a date.

What am I doing wrong?
Is there a way to convert these date fields to something Xcel will recognise
as a date?

Any suggestions will be gratefully received.

Many thanks for reading this.
Mike Valente

 

[Richard Buttrey]

I have decided to try and seek help rather than pulling any more hair out


I have a text file which I am importing successfully into Xcel 2003. (Win XP
Pro Svce Pack 2 & updates)

There are a number of date fields in this file in the format yyyymmdd. ie.
20051013
This is a format that Xcel does not recognise.
To produce a regognised date format I am using the formula
=MID(N2,7,2)&"/"&MID(N2,5,2)&"/"&MID(N2,1,4) where n2 is the cell containing
the imported date field, this produces a dd/mm/yyyy format for the date
field in a different target cell. ie. 13/10/2005
When I try and FORMAT CELLS, Date, another date format, nothing happens.
This is true if I copy and then paste values only or leave as a formula.
From this I interprit that the cell contents is not recognised by Xcel as a
date.
If, however I type in a date using the format dd/mm/yyyy, identical to what
is produced by the formula, Xcel immediately recognises this as a date and
allows the format to be changed with the FORMAT CELLS, Date etc.
Thus I am led to conclude that as the product of a formula the number,
although in dd/mm/yyyy format will not be recognised as a date.

What am I doing wrong?
Is there a way to convert these date fields to something Xcel will recognise
as a date?

Any suggestions will be gratefully received.

Many thanks for reading this.
Mike Valente

I'm assuming your text file 'date' has imported as a text value rather
than a number.

Hence:

=DATE(LEFT(N2,4),MID(N2,5,2),RIGHT(N2,2))

should sort things out.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

I don't know if this is your problem, but I have experienced this as well.
Try selecting a trouble cell containing the reformated date. Then simply
press [F2] and then press [Enter]. Now see if you can re-format the cell as a
date.

If this works, you can convert the entire column, or sheet for that matter,
by entering the following code in a standard code module and run it:

Activesheet.UsedRange.Value = Activesheet.UsedRange.Value

This code causes the new format to "take", Excel recompiles, and life is good.

As I said before, I don't know if this is your problem...HTH.

 

[Guest]

If your number is in A1, then format B1 as Date and enter:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[Mike Valente]

Dear Richard
Just the job but I don't really understand why this should be.
Many thanks
Mike

 

[Mike Valente]

Works very nicely on single cells but I couldn't get it to work on a colum.
Many thanks
Mike

I don't know if this is your problem, but I have experienced this as well.
Try selecting a trouble cell containing the reformated date. Then simply
press [F2] and then press [Enter]. Now see if you can re-format the cell
as a
date.

If this works, you can convert the entire column, or sheet for that
matter,
by entering the following code in a standard code module and run it:

Activesheet.UsedRange.Value = Activesheet.UsedRange.Value

This code causes the new format to "take", Excel recompiles, and life is
good.

As I said before, I don't know if this is your problem...HTH.

I have decided to try and seek help rather than pulling any more hair out


I have a text file which I am importing successfully into Xcel 2003. (Win
XP
Pro Svce Pack 2 & updates)

There are a number of date fields in this file in the format yyyymmdd.
ie.
20051013
This is a format that Xcel does not recognise.
To produce a regognised date format I am using the formula
=MID(N2,7,2)&"/"&MID(N2,5,2)&"/"&MID(N2,1,4) where n2 is the cell
containing
the imported date field, this produces a dd/mm/yyyy format for the date
field in a different target cell. ie. 13/10/2005
When I try and FORMAT CELLS, Date, another date format, nothing happens.
This is true if I copy and then paste values only or leave as a formula.
From this I interprit that the cell contents is not recognised by Xcel as
a
date.
If, however I type in a date using the format dd/mm/yyyy, identical to
what
is produced by the formula, Xcel immediately recognises this as a date
and
allows the format to be changed with the FORMAT CELLS, Date etc.
Thus I am led to conclude that as the product of a formula the number,
although in dd/mm/yyyy format will not be recognised as a date.

What am I doing wrong?
Is there a way to convert these date fields to something Xcel will
recognise
as a date?

Any suggestions will be gratefully received.

Many thanks for reading this.
Mike Valente

 

[Mike Valente]

Thanks for that.
Many thanks again
Mike

If your number is in A1, then format B1 as Date and enter:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))