Formatting Dates

[Lynda]

I have looked through other threads but can't find an answer to my query. I
have dates that I am unable to format. We run an SQL query at work and the
dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy
but they refuse to change. We tried the normal date format, no good, then we
tried the custom format and still no good. Does anyone have any ideas as to
how we might be able to fix this?
Cheers
Lynda

 

[Eduardo]

Hi Lynda,
What do you get, try doing text to columns for that imported dates or in an
auxiliar column enter
=Trim(A1)
to trim any blank space then copy and paste values and format it see what
happens
Hope this helps

 

[Joel]

You may be having problems with different international standards. It look
like you ar eusing English standards and the data my of originated in the US.
You also may have a single quote in front of the string which is forcing
TEXT.

You can remove the single quotes by using worksheet REPLACE (menu edit
Replace) and in the from box put in a single quote and put in the to box
nothing.

Using the datevalue function may solve the problem

=DATEVALUE(I3)

Yo also may need to us a formula to extract the year month and day.
something like this

=date(left(I3,4),mid(I3,6,2),right(I3,2))

 

[Lynda]

Thank you Eduardo, I will try this when I get back to work tomorrow and let
you know how I got on.

Cheers
Lynda

 

[Lynda]

Hi Joel, (BTW we are using 2003) I did notice when we went into the format
cells that the date format was showing the English (US) so we changed it to
English (Australia) but that didn't work either. I will also try your
suggestion when I get back to work tomorrow and let you know how I get on.

Cheers
Lynda

 

[Ron Rosenfeld]

I have looked through other threads but can't find an answer to my query. I
have dates that I am unable to format. We run an SQL query at work and the
dates came back as yyyy-mm-dd, so we were trying to format them as dd/mm/yyyy
but they refuse to change. We tried the normal date format, no good, then we
tried the custom format and still no good. Does anyone have any ideas as to
how we might be able to fix this?
Cheers
Lynda

If none of them are changing, then they are probably being imported as Text.

One solution:

Select the column of dates.
Select the Text-to-Columns wizard (Data menu) and, when you get to the
appropriate step, select YMD as the date format. After you do this, Excel
should convert them to "real" Excel dates and you should be able to format them
however you wish.
--ron

 

[Lynda]

Eduardo, Joel and Ron, thank you all. Ron, I didn't get to try your
suggestion but I will put it in my 'Computer Tips' folder for future
reference.

Thank you all once again.

Cheers
Lynda