How to identify and convert a yyyy/mm/dd date to mm/dd/yy

[Linda]

Hello there,

I have downloaded information into an Excel spreadsheet
that contains dates that are in yyyy/mm/dd format, but
there isn't a format listed in Excel for the U.S. I want
to convert the date to mm/dd/yy so that I can do
some "days between" type calculations. Any suggestions?

 

[Norman Harker]

Hi Linda!

I think I'm right in saying that yyyy/mm/dd is recognised as a date by
all versions of Excel.

Are your dates being imported as dates or as text?

Try using =ISNUMBER(CellRefForOneDate)

If it returns TRUE then you have a date and all you have to do is
apply a different format from the Format > Cells > Date format list or
custom make your own using Format > Cells > Custom.

If it returns FALSE, then the date has been imported as text.

Put 1 in a cell
Edit > Copy
Select your column of dates
Edit > Paste Special > Check "Multiply"
OK

That should return a date serial number that you can now format to a
date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Belgium (Flemish Community Holiday and
Guldensporenslag); France (La Fete de la Magdalene); International
(World Population Day); Italy (St. Rosalia Day); Mongolia (Revolution
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.