Excel wont recognize date formt

[Fendi Baba]

I export a set of data from SQL and have a column showing dates. An
example of this format is
5/13/2007 6:19:07 PM which is May, 13 2007. I need to format this
column to show just the date without the time element. but excel wont
recognise this as a date. How can I work around this?

Thanks for your kind advice.

Regards

 

[papou]

Hi Fendi
Try this:

Enter value 1 in a cell then
Edit Copy this cell
Select data in your column
Edit Paste Special
Tick Multiplication
Click OK

HTH
Cordially
Pascal

 

[Fendi Baba]

Pascal, I must have done something wrong. I did what you suggested and
it didnt work. I created a column entered 1 in a cell copied and paste
special to the date cell with multiplication ticked.

 

[Guest]

Enter two empty helper columns to the right of your date column
select the date/time date
Data-text to column-
select delimited
next
select space
next
select date MDY
enter
delete the two helper columns

 

[Guest]

Hi,

I just pasted your data into my excel and it also didn't recognise it as a
date... HOWEVER... making the string "13/5/2007..." i.e. using the day/month
order consistent with this locale fixed it...

I suggest:

1/ String manipulate it into the right order or
2/ Change the locale (your source and destination systems differ in locale -
which depending on the size of your org. and actual locations of the two
machines may not be an error), or are not both MS, or you need to structure
your SQL differently to return a day/month order that is compatible with your
locale, .or....)

Then, to get the date only you can use a custom format on the cell, or you
could use a formula in another cell (assume your value is in A1), the formula
could be =TEXT(A1,"dd/mm/yyyy")

worked here on Office XP.

Hope that helps

Julian