I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance

You can use a formula or a macro, not just formating:

=DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

and then format the formula cel as:
d/m/yyyy

The macro would do the same thing in VBA, but only use one cell, not two.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance

Thanks for the reply,
Please advise a little further..

Question 1
How would I adapt the formula if I wanted the following format
Enter 22102005 ..............get.......22/Oct/05

Question 2
I assume if I want to apply the formula to the whole column, I have to drag
it down?

Question 3
I would probably be better incorporating it as VBA.
I assume I would applie the code to the column?

Thanks in advance
"Gary''s Student" wrote:

You can use a formula or a macro, not just formating:

=DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

and then format the formula cel as:
d/m/yyyy

The macro would do the same thing in VBA, but only use one cell, not two.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance

Since your new format already has all four year digits included, the formula
is a little more simple:

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2)) in an adjacent cell
To format the result cell:

Edit Cells... Number Custom dd/mmm/yy

If your dates are in a column, then just copy the formula down the adjacent
column. There is a little trick to enter formulae in many cells at the same
time. Usually we just type a formula and then touch the ENTER key. The
trick involves

1. high-lighting all the cells in column B with the mouse
2. enter the formula
3. finish by touching the CNTRL-ENTER combination rather than just ENTER


The VBA approach does not need any adjacent cells or columns. You would
just enter your dates and then run the macro.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

Thanks for the reply,
Please advise a little further..

Question 1
How would I adapt the formula if I wanted the following format
Enter 22102005 ..............get.......22/Oct/05

Question 2
I assume if I want to apply the formula to the whole column, I have to drag
it down?

Question 3
I would probably be better incorporating it as VBA.
I assume I would applie the code to the column?

Thanks in advance
"Gary''s Student" wrote:

You can use a formula or a macro, not just formating:

=DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

and then format the formula cel as:
d/m/yyyy

The macro would do the same thing in VBA, but only use one cell, not two.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance

Thanks for the details Gary's Student


"Gary''s Student" wrote:

Since your new format already has all four year digits included, the formula
is a little more simple:

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2)) in an adjacent cell
To format the result cell:

Edit Cells... Number Custom dd/mmm/yy

If your dates are in a column, then just copy the formula down the adjacent
column. There is a little trick to enter formulae in many cells at the same
time. Usually we just type a formula and then touch the ENTER key. The
trick involves

1. high-lighting all the cells in column B with the mouse
2. enter the formula
3. finish by touching the CNTRL-ENTER combination rather than just ENTER


The VBA approach does not need any adjacent cells or columns. You would
just enter your dates and then run the macro.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

Thanks for the reply,
Please advise a little further..

Question 1
How would I adapt the formula if I wanted the following format
Enter 22102005 ..............get.......22/Oct/05

Question 2
I assume if I want to apply the formula to the whole column, I have to drag
it down?

Question 3
I would probably be better incorporating it as VBA.
I assume I would applie the code to the column?

Thanks in advance
"Gary''s Student" wrote:

You can use a formula or a macro, not just formating:

=DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

and then format the formula cel as:
d/m/yyyy

The macro would do the same thing in VBA, but only use one cell, not two.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance

See Chip Pearson's site for event code for quick entry of dates and/or times.

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

Alos Ron de Bruin's site for the QDE add-in.

http://www.rondebruin.nl/qde.htm


Gord Dibben MS Excel MVP

On Sun, 16 Sep 2007 05:00:01 -0700, Dermot
wrote:

Thanks for the reply,
Please advise a little further..

Question 1
How would I adapt the formula if I wanted the following format
Enter 22102005 ..............get.......22/Oct/05

Question 2
I assume if I want to apply the formula to the whole column, I have to drag
it down?

Question 3
I would probably be better incorporating it as VBA.
I assume I would applie the code to the column?

Thanks in advance
"Gary''s Student" wrote:

You can use a formula or a macro, not just formating:

=DATE(2000+RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))

and then format the formula cel as:
d/m/yyyy

The macro would do the same thing in VBA, but only use one cell, not two.
--
Gary''s Student - gsnu200745


"Dermot" wrote:

I have checked many links to date formatting but can't find a solution.
I must be missing the point somewhere as I don't believe my question below
can be undocumented.

How do I format dates so I just enter numbers without any formating?
For example:
If I Enter 251005 in the cell I would like it to auto format to 25/10/2005.

Thanks in advance