Convert mm/dd/yyyy to mmddyy

[Annette]

I am pulling data out of a student information system in a txt format, then
open it in Excel to format each field as necessary to submit to a testing
center for data purposes. The birthdate comes out as (for example) as
7/23/1996; once it's opened in Excel I use the Format Cells function to
change it to 072396 per the testing center's parameters. When I validate the
spreadsheet, the value changes to 35269. What do I need to do differently to
have the value remain 072396 after validation?

 

[Mike H]

Hi,

In excel dates are numbers and the number 35269 is 23 July 1996 so
formatting this number as a date should get you to where you want to be.

Mike

 

[Annette]

When I do that, it appears as 072396 in the cell but 7/23/96 in the formula
bar. When I validate it, it becomes 35269 again.

 

[Mike H]

Annette,

It will display in the cell precisely how you tell it to. so 7/23/96 will
show in the formula bar but if you format as mmddyy you will se 072396 in the
cell.

Pick the standard date format of mm/dd/yy and you will see the slashes in
the cell

Mike

 

[T. Valko]

What does "When I validate it" mean?

 

[Annette]

The testing center has a template that I have to use to submit the student
data to them, and each field has to be formatted to their specs. Then, after
I paste the data into the template, there's a button in a custom toolbar that
I click to validate the data, that runs a macro to make sure everything is
formatted the way they want it, and errors are highlighted. As soon as I do
that the dates change to the weird from the time of Jesus format and are
highlighted as errors.

 

[Luke M]

Since your transferring the data to another workbook (which isn't treating
dates the way XL is designed to treat dates) you may need to use one of the
following:

=TEXT(TEXT(A2,"mmddyy"),"000000")
(Which gives you a 6 character text string, but might fit the formatting
template is looking for)

or

=VALUE(TEXT(TEXT(A2,"mmddyy"),"000000"))
(Which gives a 5-6 character number, but you could format the cell to still
display leading zeroes.)

 

[TheExcelAddict]

Try this...

=TEXT(A1,"mmddyy")
Copy and Paste Values

Francis Hayes
www.TheExcelAddict.com

 

[Annette]

YES!!!!!! At last!!! Thank you so much!!!

Since your transferring the data to another workbook (which isn't treating
dates the way XL is designed to treat dates) you may need to use one of the
following:

=TEXT(TEXT(A2,"mmddyy"),"000000")
(Which gives you a 6 character text string, but might fit the formatting
template is looking for)

or

=VALUE(TEXT(TEXT(A2,"mmddyy"),"000000"))
(Which gives a 5-6 character number, but you could format the cell to still
display leading zeroes.)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Annette]

That worked, too! Thank you!!!

Try this...

=TEXT(A1,"mmddyy")
Copy and Paste Values

Francis Hayes
www.TheExcelAddict.com

 

[KOMI AHIAKPO]

Hi Francis ,
you meant by r post =TEXT(A1,"mmddyy")or =3DTEXT(A1,"mmddyy")?