On 13 Sep 2005 11:25:04 -0700, "tom" wrote:

Here's one that I cannot figure out.

A range contains DOB cells that currently look like this:
MonthDayYear
So, for example a cell contains 311983, and we, by process of reason,
know that the date is March 1, 1983.

I need to change this so that the cells end up with a YYYYMMDD value,
always with 8 characters. The above cell should end up with a value of
19830301.

What is the process by which I would write a VBA routine that moves the
last four characters to the front of the value, and adds 0's in the
appropriate place?

Thanks for your help.

-tom

You can't do it unless you resolve the ambiguity by requiring two digit dates.

In other words, if your range shows 3011983 instead of what you have, then it
is possible:

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

and format the cell as Format/Cells/Number/Custom Type: YYYYMMDD

or use this formula (which returns a TEXT value):

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


--ron