Changing text to a date

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

I am trying to help someone convert a large number of
records quickly. They pulled information (they copied
and pasted, I think) from various databases into one
Excel file. In the Date column in Excel recognizes some
of the data as dates, but others appear to be coming
across as text even though Excel says the cells are
formatted as a date. It looks like the way the date was
entered in the other program(s) was 01 Jan 43 (meaning
Jan 1, 1943) with spaces between the day, month, and
year. Anyone have a quick way to convert these "text"
dates to real dates? Nothing I've tried is working?
Thanks in advance.
 
Hi
try the following in the adjacent column (lets say in column B, cell
B1)
=DATEVALUE(LEFT(A1,2) & "-" & MID(A1,4,3) & "-" & RIGHT(A1,2))
format this cell as date and copy down
 
Hi Connie!

Check both types of dates very carefully before doing anything. If
possible, check back to the source to verify that the "translated"
dates are in fact correct.

I fear that it is a Regional Settings problem and that even the dates
that are translated as dates are wrong. You'll see if this is the case
if the dates that are being translated are all dates before the 13th
of the month. Where the date is after the 12th of the Month, Excel
sees that the 13th etc month doesn't exist and treats it as text.

If you have the information still available at source you can:

Change your Regional Settings.
Re-import the data
Change back your Regional Settings.

If currently using mmddyy, you need to change it to ddmmyy before
importing and change back afterwards. If currently using ddmmyy, you
need to change it to mmddyy before importing and change back
afterwards.

If the original information is not available to you then:

!!!!Save the file and work on the backup!!!!

Insert a record numbers column so you can get your data back to the
current order.
Sort the data on the dates column.
This should give you a batch of "wrong" dates that are recognised by
Excel as dates and a batch of "text" dates.

Now you need a helper column
For the wrongly translated dates use:

=DATE(YEAR(A1),MONTH(DAY(A1)),DAY(MONTH(A1))

For the text dates use:

=DATE(RIGHT(A1,2),VLOOKUP(A1,{"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;
"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,FALSE),L
EFT(A1,2)

Now check back with the source that both types of dates are OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks for your formula. I was trying to use DATEVALUE
and LEFT and RIGHT, but I didn't put them together quite
like you did. I love learning new ways to use the
functions in Excel! Anyway, I had the person email the
file. After looking more closely, I realized that the
spacing in the "date" was not the problem (also the date
was actually 4 digits not 2 as I first wrote). I think
the problem is that the dates I am having problems with
are from the 1800's. And as far as I can tell Excel does
not recognize dates before Jan 1, 1900. Is that
correct? If you know of some way to work with dates
earlier than 1-1-1900 (for sorting and calculating
purposes), please let me know. Otherwise, I think we can
concoct something to get them to appear the way we want
and still be able to sort in "date" order. Sorry for
misleading you on my first explanation of the problem.
 
Back
Top