How do i format a 5 to 6 digit number into the correct date?

G

Guest

Currently have thousands of cells entered as 61200 (June 12, 2000) or 101200
(October 12, 2000). When these cells are formated as a dates, the dates show
incorrectly. To save time, need to format the cells as dates without
manually entering two / to seperate the groups of characters as 6/12/00 or
10/12/00.

Thanks
 
A

Arvi Laanemets

Hi

With date in A1, use formula in additional column to convert the number to
date:
=1*(LEFT(A1,LEN(A1)-4) & "/" & MID(A1,LEN(A1)-3,2) &"/" & RIGHT(A1,2))

NB! This works only, when your dates are currently in "format" 'mddyyyy' -
i.e.
11100 means always 1/11/00, and never 11/1/00. When contrary, then the only
way you have left is manual editing.


Arvi Laanemets


"date cell configuration" <date cell
(e-mail address removed)> wrote in message
news:[email protected]...
 
G

Guest

If you really need them as dates use a helper column and ente
=DATE(2000+VALUE(RIGHT(A4,2)),VALUE(LEFT(A4,(IF(LEN(A4)=6,2,1)))),VALUE(MID(A4,IF(LEN(A4)=6,3,2),2)))
(If all of them are 2000 or later)
IF you just need the display to look like a date
use a custom format of #"/"##"/"##
and the display will look like 6/12/00 it will not be a date though
 
R

Ron Rosenfeld

On Fri, 10 Jun 2005 10:28:03 -0700, "date cell configuration" <date cell
Currently have thousands of cells entered as 61200 (June 12, 2000) or 101200
(October 12, 2000). When these cells are formated as a dates, the dates show
incorrectly. To save time, need to format the cells as dates without
manually entering two / to seperate the groups of characters as 6/12/00 or
10/12/00.

Thanks

Assuming your Windows regional settings are US:

=--TEXT(A1,"00\/00\/00")

will convert those numbers to Excel dates.


--ron
 
G

Guest

If the day always have 2 digits so that 06/01/00 displays as 60100 then the
best way is not formulas, just select the column
do data>text to columns. click next twice to get to step 3, select Date
under column data format and select MDY, click finish

Regards,

Peo Sjoblom
 

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

Top