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

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

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
wrote in message
...
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

If you really need them as dates use a helper column and enter
=DATE(2000+VALUE(RIGHT(A4,2)),VALUE(LEFT(A4,(IF(LE N(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

"date cell configuration" wrote:

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

On Fri, 10 Jun 2005 10:28:03 -0700, "date cell configuration" <date cell
wrote:

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

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 datatext to columns. click next twice to get to step 3, select Date
under column data format and select MDY, click finish

Regards,

Peo Sjoblom

"date cell configuration" wrote:

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