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

L

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?
 
N

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

converting dd/mm/yy to mm/dd/yyyy 3
can I change the date format as dd-mm-yy 7
Converting mm/dd/yyyy to yyyy/mm/dd 6
Excel is seeing the date as dd/mm/yyyy 4
How to enter a date in DD-MM-YYYY format 2
convert date mm/dd/yyyy to dd/mm/yyyy 2
how do I change the date format from yyyymmdd to mm/dd/yyyy 4
Forcing a date entry format of dd/mm/yyyy 1

Top