Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
Change text into time | Excel Discussion (Misc queries) | |||
Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
Text Color Change prompted by different columns | Excel Worksheet Functions | |||
How do I change the default cell formatting to be text? | Excel Discussion (Misc queries) |