Date format

[Robert]

Hi all,

I have a problem with a date format, and I have no clue how to
convert
it into dd/mm/yyyy
It now says for example 04/16/2013 however the Cell Format says
"General", defining a date format under Format/Cells/Custom being dd/
mm/yyyy does not work.
I also tried Date/Text to columns...next/next change "Column Date
Format" into Date: DMY does not help either.


Does someone perhaps now another way of re-formatting a date?


Many thanks for any suggestion!


Kind regards,
Robert

 

[Dave Peterson]

First, be careful.

You may have values in that range of dates that are really dates--but not what
you expect.

If you format that range in an unambiguous date format: mmmm dd, yyyy
do any of the cells display a date.

If they do, then you may find that a date originally formatted to look like
01/02/07 doesn't mean what you want. In my USA settings, it would mean Jan 2,
2007. But you may find that you see February 01, 2007.

If that's the case, I'd bring the data in as text. If you bring the data in as
General, then excel will convert anything that looks like a date to a date
(using your windows short date settings--and with no regard to what the value
actually represents!).

Depending on what the original source of the data is, you can use different
techniques to convert them to dates--and the real dates that you need.

For instance, if the data came from a .txt file (or .csv), you can use this:

(Rename the .csv file to .txt first)
Open excel
file|open the .txt file
You'll see the data import wizard
You can choose date for this field
Choose a format that matches the data (mdy), not what you want.
and finish up the wizard

Then format that field(s) to display the way you want (dd/mm/yyyy).

========
If you're lucky and all the displayed values in that range don't change when you
give it that unambiguous date format (mmmm dd, yyyy), you can select that range
(single column at a time), then use:

Data|text to columns

It works just like the text import wizard.