Multiple Date/Time Formats
 

Hi

I receive a data feed in the format of a workbook and the data that is
supplied has different data sources and as a result the time and date field
are in different formats:

29/09/2005 07:23 AM
29/09/2005 07:23 AM
29/09/2005 07:23 AM
09-05-2005
09-05-2005
09-05-2005
09-05-2005

It would appear that the top three may be associated with text and the
bottom with numeric.

I have tried the obvious ie format special DD/MM/YYYY but to no avail.

Please could you help with a quick step by step answer to resolve this issue.

Hi

At start save your workbook. When something goes wrong way later, you can
unmade all meanwhile changes, closing the workbook withour saving.

Format the range with dates in valid date format (or General or any other
numeric format).
Enter the number 1 into some free cell, and copy it.
Select the range with dates, and PasteSpecial.Multily - all entries must
convert to numbers (When not, then this entry was in format, not recognized
by Excel as date).
Format the range as date (again).
Delete the number 1.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"colin" wrote in message
...
Hi

I receive a data feed in the format of a workbook and the data that is
supplied has different data sources and as a result the time and date
field
are in different formats:

29/09/2005 07:23 AM
29/09/2005 07:23 AM
29/09/2005 07:23 AM
09-05-2005
09-05-2005
09-05-2005
09-05-2005

It would appear that the top three may be associated with text and the
bottom with numeric.

I have tried the obvious ie format special DD/MM/YYYY but to no avail.

Please could you help with a quick step by step answer to resolve this
issue.

Hi Arvi,

Many thanks for the work around

Much appreciated

Rgds

Colin

"Arvi Laanemets" wrote:

Hi

At start save your workbook. When something goes wrong way later, you can
unmade all meanwhile changes, closing the workbook withour saving.

Format the range with dates in valid date format (or General or any other
numeric format).
Enter the number 1 into some free cell, and copy it.
Select the range with dates, and PasteSpecial.Multily - all entries must
convert to numbers (When not, then this entry was in format, not recognized
by Excel as date).
Format the range as date (again).
Delete the number 1.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"colin" wrote in message
...
Hi

I receive a data feed in the format of a workbook and the data that is
supplied has different data sources and as a result the time and date
field
are in different formats:

29/09/2005 07:23 AM
29/09/2005 07:23 AM
29/09/2005 07:23 AM
09-05-2005
09-05-2005
09-05-2005
09-05-2005

It would appear that the top three may be associated with text and the
bottom with numeric.

I have tried the obvious ie format special DD/MM/YYYY but to no avail.

Please could you help with a quick step by step answer to resolve this
issue.