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

  • Thread starter date cell configuration
  • Start date
D

date cell configuration

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
[email protected]> wrote in message
news:[email protected]...
 
B

bj

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
Click to expand...

Assuming your Windows regional settings are US:

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

will convert those numbers to Excel dates.


--ron
 
P

Peo Sjoblom

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
 
Top