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

A little more info is needed here. What if the date was 21 January 1983?
Would it be

1211983

?? And if so, how would that differ from 1 December 1983?


In article .com,
"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

A little more info is needed here. What if the date was 21 January 1983?
Would it be

1211983
?? And if so, how would that differ from 1 December 1983?

I knew I didn't go into enough detail - unfortunately I wasn't clear on
the format.

It turns out that if a day is only one digit, then it is prepended with
a 0. That way there is no confusion.

So, 1 Dec 1983 looks like 12011983, whereas 21 Jan 1983 looks like
1211983.

Does this mean I only have to add a 0 if len(string) is less than 8? I
guess so . . .

Could you provide a little refresher on moving four characters to the
beginning of a string?

Thanks

-tom

This should be close...

Sub GiveItATry()
MsgBox MakeDate("431983")
MsgBox MakeDate("1221983")
MsgBox MakeDate("12021983")

End Sub

Public Function MakeDate(ByVal InputDate As String) As String
Dim strReturnValue As String

Select Case Len(InputDate)
Case 6
strReturnValue = Mid(InputDate, 3, 4) & "0" & _
Left(InputDate, 1) & "0" & Mid(InputDate, 2, 1)
Case 7
strReturnValue = Mid(InputDate, 4, 4) & "0" & _
Left(InputDate, 1) & Mid(InputDate, 2, 2)
Case 8
strReturnValue = Mid(InputDate, 5, 4) & _
Left(InputDate, 2) & Mid(InputDate, 2, 2)
Case Else
strReturnValue = "Date Error"
End Select
MakeDate = strReturnValue
End Function

--
HTH...

Jim Thomlinson


"tom" wrote:

A little more info is needed here. What if the date was 21 January 1983?
Would it be

1211983
?? And if so, how would that differ from 1 December 1983?

I knew I didn't go into enough detail - unfortunately I wasn't clear on
the format.

It turns out that if a day is only one digit, then it is prepended with
a 0. That way there is no confusion.

So, 1 Dec 1983 looks like 12011983, whereas 21 Jan 1983 looks like
1211983.

Does this mean I only have to add a 0 if len(string) is less than 8? I
guess so . . .

Could you provide a little refresher on moving four characters to the
beginning of a string?

Thanks

-tom

Thanks for everyone's reponses. As usual, it was incredibly helpful in
leading me to the final solution.

Here is what worked:

Sub YYYYMMDD()

Dim DOB As String
Dim yr As String
Dim m As String
Dim d As String
Dim cell As Range


For Each cell In Range("s22:s395")
DOB = cell.Text
yr = Right(DOB, 4)
If Len(DOB) = 8 Then
m = Left(DOB, 2)
d = Mid(DOB, 3, 2)
cell.Value = yr & m & d

ElseIf Len(DOB) = 7 Then
m = "0" & Left(DOB, 1)
d = Mid(DOB, 2, 2)
cell.Value = yr & m & d
End If

Next cell

End Sub


I know that the mishmash between text and value isn't right - I just
don't know how to overcome it. The length had to be a minumum of 7
because of the 0 that prepends days that are only one digit. I have no
idea what I would have done if that weren't the case.

Thanks again!

-tom

And what happens with:

1211983

Jan 21, 1983
Dec 1, 1983

????



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

--

Dave Peterson

One of the things you might be looking for is

Dim m as string, d as string, yr as string

With the number in A1
yr = Right(Range("A1"),4)

If Len(Range("A1")=6 then
m = "0" & Left(Range("A1"),1)
d = "0" & Mid(Range("A1"),2,1)

Range("A2")= yr & m & d
End If

If A1 is MMDDYYYY than it is easy with the above idea.

If Len(Range("A1")=8 then
m = Left(Range("A1"),2)
d = "0" & Mid(Range("A1"),3,2)

But am not sure when len = 7
do you have mddyyyy or mmdyyyy

You can start by checking if the first character = 1
than you have either 1, 10, 11, 12
if it is not = 1 than you have a single character month

if the second character is 2 than you have a single character month. (1 to
9)
if the first character is 1 and the second is 0 than you have 10

Not sure how to capture 11 & 12

hope this helps...

--
steveB

Remove "AYN" from email to respond
"tom" wrote in message
oups.com...
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

What should 1121983 become? Jan 12th or Nov 2nd. There is no way to determine
from the information you have posted.
--
HTH...

Jim Thomlinson


"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

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