Dates in Excel 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
Thanks for the details Gary's Student


Gary''s Student said:
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.
Click to expand...
 
Back
Top