Change m/d/yyyy to mmddyyyy in text
 

My favorite gurus,
I'm trying to upload to the Social Security Administration our employees to
check their SSN and make sure it is correct. The part that is kicking my @$$
is changing the DOB to their required mmddyyyy format which must be text as
this turns into a fixed length text file (with the help of the handy formula
I found through the newsgroup.)

I can use the =Month(a2) to get the month and =day(a2) to get the day. The
problem I'm having is making all the one digit months and days into 2 digit
month and days. I've tried formatting them and then making it text and it
reverts to 1 digit again. This is driving me crazy!

So, I want:

8/1/1964 to be 08011964 as text.

Your help is always appreciated. I rave about the newsgroups and love
finding the links so I can educate myself.

Thanks,
Alicia

Change m/d/yyyy to mmddyyyy in text
 

Eureeka! I found it! (I kept looking in the posts) The magic formula is:

=TEXT(MONTH(A3),"00")&TEXT(DAY(A3),"00")&RIGHT(YEA R(A3),4)

Once again, the newsgroup save my sanity.

Thanks,
Alicia

"Alicia" wrote:

My favorite gurus,
I'm trying to upload to the Social Security Administration our employees to
check their SSN and make sure it is correct. The part that is kicking my @$$
is changing the DOB to their required mmddyyyy format which must be text as
this turns into a fixed length text file (with the help of the handy formula
I found through the newsgroup.)

I can use the =Month(a2) to get the month and =day(a2) to get the day. The
problem I'm having is making all the one digit months and days into 2 digit
month and days. I've tried formatting them and then making it text and it
reverts to 1 digit again. This is driving me crazy!

So, I want:

8/1/1964 to be 08011964 as text.

Your help is always appreciated. I rave about the newsgroups and love
finding the links so I can educate myself.

Thanks,
Alicia

Change m/d/yyyy to mmddyyyy in text
 

=TEXT(A1,"mmddyyyy")


"Alicia" wrote:

My favorite gurus,
I'm trying to upload to the Social Security Administration our employees to
check their SSN and make sure it is correct. The part that is kicking my @$$
is changing the DOB to their required mmddyyyy format which must be text as
this turns into a fixed length text file (with the help of the handy formula
I found through the newsgroup.)

I can use the =Month(a2) to get the month and =day(a2) to get the day. The
problem I'm having is making all the one digit months and days into 2 digit
month and days. I've tried formatting them and then making it text and it
reverts to 1 digit again. This is driving me crazy!

So, I want:

8/1/1964 to be 08011964 as text.

Your help is always appreciated. I rave about the newsgroups and love
finding the links so I can educate myself.

Thanks,
Alicia

Change m/d/yyyy to mmddyyyy in text
 

Sub dateformat()
For Each c In Selection
c.Offset(, 1).NumberFormat = "@"
c.Offset(, 1) = Format(c, "mmddyyyy")
Next c
End Sub

--
Don Guillett
SalesAid Software

"Alicia" wrote in message
...
My favorite gurus,
I'm trying to upload to the Social Security Administration our employees
to
check their SSN and make sure it is correct. The part that is kicking my
@$$
is changing the DOB to their required mmddyyyy format which must be text
as
this turns into a fixed length text file (with the help of the handy
formula
I found through the newsgroup.)

I can use the =Month(a2) to get the month and =day(a2) to get the day.
The
problem I'm having is making all the one digit months and days into 2
digit
month and days. I've tried formatting them and then making it text and it
reverts to 1 digit again. This is driving me crazy!

So, I want:

8/1/1964 to be 08011964 as text.

Your help is always appreciated. I rave about the newsgroups and love
finding the links so I can educate myself.

Thanks,
Alicia

Change m/d/yyyy to mmddyyyy in text
 

Sloth,
Yep, that works too and it certainly is more elegant! Thanks!

Alicia

"Sloth" wrote:

=TEXT(A1,"mmddyyyy")


"Alicia" wrote:

My favorite gurus,
I'm trying to upload to the Social Security Administration our employees to
check their SSN and make sure it is correct. The part that is kicking my @$$
is changing the DOB to their required mmddyyyy format which must be text as
this turns into a fixed length text file (with the help of the handy formula
I found through the newsgroup.)

I can use the =Month(a2) to get the month and =day(a2) to get the day. The
problem I'm having is making all the one digit months and days into 2 digit
month and days. I've tried formatting them and then making it text and it
reverts to 1 digit again. This is driving me crazy!

So, I want:

8/1/1964 to be 08011964 as text.

Your help is always appreciated. I rave about the newsgroups and love
finding the links so I can educate myself.

Thanks,
Alicia