I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

If you're going to enter these in a column, then you can use TTC (Text To
Columns) to transform them into XL recognizable dates.
You'll need all 6 digits to avoid confusion, or else TTC will simply *not*
return a date, or return a wrong date.

Select the column of data, and then,
<Data <Texr To Columns <Next <Next

And under "Column Data Format"
Click on "Date",
And choose the date format you currently *have* (not what you want them to
be when you're done).

The numbers you used in your example (020104) could actually represent *all*
date formats.

So you'll have to stipulate whether they're *presently*:
mdy
dmy
or even
ymd

Then <Finish

TTC will kick out any numbers that don't fit the format you picked.

Now you can select the column of dates, and re-format them to the display
you wish if you're not satisfied with the default format TTC used.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Helpless" wrote in message
...
I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

Hi Helpless,

You can just type in 2/1/4 and Excel will autocorrect to 02/01/04
anyway, which is marginally quicker than typing 020104.

You could also use the LEFT, MID, RIGHT and CONCATENATE functions to do
this aswell:

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)

This will only be recognised in another cell.

All the best,


Roly


--
roly
------------------------------------------------------------------------
roly's Profile: http://www.excelforum.com/member.php...o&userid=24631
View this thread: http://www.excelforum.com/showthread...hreadid=401727

Check out Chip Pearson's site for code to "quick-enter" times and dates in the
manner you ask for.


Gord Dibben Excel MVP



On Sat, 3 Sep 2005 14:35:32 -0700, "Helpless"
wrote:

I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

And the URL is

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord

On Sat, 03 Sep 2005 16:50:08 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Check out Chip Pearson's site for code to "quick-enter" times and dates in the
manner you ask for.


Gord Dibben Excel MVP



On Sat, 3 Sep 2005 14:35:32 -0700, "Helpless"
wrote:

I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

Thank you

"RagDyeR" wrote:

If you're going to enter these in a column, then you can use TTC (Text To
Columns) to transform them into XL recognizable dates.
You'll need all 6 digits to avoid confusion, or else TTC will simply *not*
return a date, or return a wrong date.

Select the column of data, and then,
<Data <Texr To Columns <Next <Next

And under "Column Data Format"
Click on "Date",
And choose the date format you currently *have* (not what you want them to
be when you're done).

The numbers you used in your example (020104) could actually represent *all*
date formats.

So you'll have to stipulate whether they're *presently*:
mdy
dmy
or even
ymd

Then <Finish

TTC will kick out any numbers that don't fit the format you picked.

Now you can select the column of dates, and re-format them to the display
you wish if you're not satisfied with the default format TTC used.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Helpless" wrote in message
...
I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

Thank you

"Gord Dibben" wrote:

Check out Chip Pearson's site for code to "quick-enter" times and dates in the
manner you ask for.


Gord Dibben Excel MVP



On Sat, 3 Sep 2005 14:35:32 -0700, "Helpless"
wrote:

I am trying to figure a way to type in a date in pure numbers such as 020104
and for it to show in the cell as the date 02/04/04 in this format.

Please help

Thank you

"roly" wrote:


Hi Helpless,

You can just type in 2/1/4 and Excel will autocorrect to 02/01/04
anyway, which is marginally quicker than typing 020104.

You could also use the LEFT, MID, RIGHT and CONCATENATE functions to do
this aswell:

=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)

This will only be recognised in another cell.

All the best,


Roly


--
roly
------------------------------------------------------------------------
roly's Profile: http://www.excelforum.com/member.php...o&userid=24631
View this thread: http://www.excelforum.com/showthread...hreadid=401727