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?

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" wrote:

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?

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" wrote:

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" wrote:

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?

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

"Annette" wrote:

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" wrote:

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" wrote:

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?

What does "When I validate it" mean?

--
Biff
Microsoft Excel MVP


"Annette" wrote in message
...
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" wrote:

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" wrote:

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?

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.

"T. Valko" wrote:

What does "When I validate it" mean?

--
Biff
Microsoft Excel MVP


"Annette" wrote in message
...
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" wrote:

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" wrote:

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?

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" wrote:

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.

"T. Valko" wrote:

What does "When I validate it" mean?

--
Biff
Microsoft Excel MVP


"Annette" wrote in message
...
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" wrote:

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" wrote:

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?

Try this...

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

Francis Hayes
www.TheExcelAddict.com


On Sep 22, 4:11*pm, Annette wrote:
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?

That worked, too! Thank you!!!

"TheExcelAddict" wrote:

Try this...

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

Francis Hayes
www.TheExcelAddict.com


On Sep 22, 4:11 pm, Annette wrote:
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?