Date Formats

I have a spreadsheet sent to me and it has a column of dates in the following
format - mm/dd/yy
I would like to change them to yyyy/dd/mm but I can't seem to change
existing format. Any ideas on how this is done?

Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
If they are:

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))
and format as required

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Greenback" <[email protected]> wrote in message news:[email protected]...
|I have a spreadsheet sent to me and it has a column of dates in the following
| format - mm/dd/yy
| I would like to change them to yyyy/dd/mm but I can't seem to change
| existing format. Any ideas on how this is done?

Niek,thanks for the reply, but I am unsure of what you mean (not an expert on
excel at all)...can you clarify?...thanks

"Niek Otten" wrote:

> Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
> If they are:
>
> =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))
> and format as required
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Greenback" <[email protected]> wrote in message news:[email protected]...
> |I have a spreadsheet sent to me and it has a column of dates in the following
> | format - mm/dd/yy
> | I would like to change them to yyyy/dd/mm but I can't seem to change
> | existing format. Any ideas on how this is done?
>
>
>

Assume your date is in A1. In B1, enter this formula:

=ISTEXT(A1)
If you get TRUE as a result, than your date is no real Excel dat, but is text.
In that case, enter the formula I suggested.
In both cases, Format>Cells>Number tab, choose Date, and choose a format from the list that suits your needs.

If no such format is in the list:

Format>Cells>Number tab, choose Custom. In the Type box, enter "yyyy/dd/mm" (without the quotes)

BTW, what do you see if you format the cell as General?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Greenback" <[email protected]> wrote in message news:[email protected]...
| Niek,thanks for the reply, but I am unsure of what you mean (not an expert on
| excel at all)...can you clarify?...thanks
|
| "Niek Otten" wrote:
|
| > Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
| > If they are:
| >
| > =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))
| > and format as required
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "Greenback" <[email protected]> wrote in message news:[email protected]...
| > |I have a spreadsheet sent to me and it has a column of dates in the following
| > | format - mm/dd/yy
| > | I would like to change them to yyyy/dd/mm but I can't seem to change
| > | existing format. Any ideas on how this is done?
| >
| >
| >

Just because data looks like dates, doesn't make them dates.

If you tried changing the format and the display didn't change, then Niek was
guessing that your data isn't really a date. (A real date would show the change
in format.)

Niek suggested putting a formula into another column and creating the date that
way from the cell with the "text date".

Another way is to select that column of dates and do
Data|Text to columns
fixed width
Don't add any lines and remove any that excel added
choose mdy as the date format.

This will convert that selection to real dates.

Now you should be able to format those cells the way you want.

Greenback wrote:
>
> Niek,thanks for the reply, but I am unsure of what you mean (not an expert on
> excel at all)...can you clarify?...thanks
>
> "Niek Otten" wrote:
>
> > Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
> > If they are:
> >
> > =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))
> > and format as required
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> > Microsoft MVP - Excel
> >
> > "Greenback" <[email protected]> wrote in message news:[email protected]...
> > |I have a spreadsheet sent to me and it has a column of dates in the following
> > | format - mm/dd/yy
> > | I would like to change them to yyyy/dd/mm but I can't seem to change
> > | existing format. Any ideas on how this is done?
> >
> >
> >

--

Dave Peterson

Dave/Nick....many thanks for you tips. I went with the Data/Text to Columns
option and it worked a treat.

"Dave Peterson" wrote:

> Just because data looks like dates, doesn't make them dates.
>
> If you tried changing the format and the display didn't change, then Niek was
> guessing that your data isn't really a date. (A real date would show the change
> in format.)
>
> Niek suggested putting a formula into another column and creating the date that
> way from the cell with the "text date".
>
> Another way is to select that column of dates and do
> Data|Text to columns
> fixed width
> Don't add any lines and remove any that excel added
> choose mdy as the date format.
>
> This will convert that selection to real dates.
>
> Now you should be able to format those cells the way you want.
>
> Greenback wrote:
> >
> > Niek,thanks for the reply, but I am unsure of what you mean (not an expert on
> > excel at all)...can you clarify?...thanks
> >
> > "Niek Otten" wrote:
> >
> > > Maybe they aren't real Excel dates, but text. You can check with the ISTEXT() function.
> > > If they are:
> > >
> > > =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,4,2))
> > > and format as required
> > >
> > > --
> > > Kind regards,
> > >
> > > Niek Otten
> > > Microsoft MVP - Excel
> > >
> > > "Greenback" <[email protected]> wrote in message news:[email protected]...
> > > |I have a spreadsheet sent to me and it has a column of dates in the following
> > > | format - mm/dd/yy
> > > | I would like to change them to yyyy/dd/mm but I can't seem to change
> > > | existing format. Any ideas on how this is done?
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>