Dates won't stay formated

M

marketerinco

I have a problem that I have been working on for days. I need my date fields
to read Ex: 08/01/2006 instead of 8/01/2006. I went into format field and
selected a custom format and was able to change it to the correct format.
When I save it and try to import it into an email program I get a message
that it's not formatted properly because it is seeing the backend formatting
which has the number equivilant.

Is there any way to cut and past into a new document and only take the final
outcome not the format? I have spent about 3 hours today on this and by
morning my client will be a little upset that this isn't done.

help!!! Thanks -
 
N

Niek Otten

|I have a problem that I have been working on for days. I need my date fields
| to read Ex: 08/01/2006 instead of 8/01/2006. I went into format field and
| Ex: 08/01/2006 instead of 8/01/2006
and was able to change it to the correct format.
| When I save it and try to import it into an email program I get a message
| that it's not formatted properly because it is seeing the backend formatting
| which has the number equivilant.
|
| Is there any way to cut and past into a new document and only take the final
| outcome not the format? I have spent about 3 hours today on this and by
| morning my client will be a little upset that this isn't done.
|
| help!!! Thanks -

<Ex: 08/01/2006 instead of 8/01/2006>

Do you really mean you need "Ex: " in front of the original format?

<was able to change it to the correct format>

What format?
 
M

marketerinco

Sorry what I need is:
08/01/2006
instead of 8/01/2006

8/01/2006 is how the field was originally and I needed to get the field to
08/01/2006. I can successfuly do that but the program we use for our email
is seeing the code.
 
R

Rookie 1st class

Control Panel> Regional and Language Options.
Configure short date as you want and your problem will disappear.
Been there done that
Lou
 
M

marketerinco

No luck. It still will not import. It is still seeing the back end
formatting that Excel does to the field.
 
N

Niek Otten

< I can successfuly do that but the program we use for our email is seeing the code.>

????????????

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry what I need is:
| 08/01/2006
| instead of 8/01/2006
|
| 8/01/2006 is how the field was originally and I needed to get the field to
| 08/01/2006. I can successfuly do that but the program we use for our email
| is seeing the code.
|
| "Niek Otten" wrote:
|
| >
| > | > |I have a problem that I have been working on for days. I need my date fields
| > | to read Ex: 08/01/2006 instead of 8/01/2006. I went into format field and
| > | Ex: 08/01/2006 instead of 8/01/2006
| > and was able to change it to the correct format.
| > | When I save it and try to import it into an email program I get a message
| > | that it's not formatted properly because it is seeing the backend formatting
| > | which has the number equivilant.
| > |
| > | Is there any way to cut and past into a new document and only take the final
| > | outcome not the format? I have spent about 3 hours today on this and by
| > | morning my client will be a little upset that this isn't done.
| > |
| > | help!!! Thanks -
| >
| > <Ex: 08/01/2006 instead of 8/01/2006>
| >
| > Do you really mean you need "Ex: " in front of the original format?
| >
| > <was able to change it to the correct format>
| >
| > What format?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| >
 
M

marketerinco

Excel is doing some crazy conversion so that a date shows a number value not
the formated value. For example Excel has this as the date 37622
instead of it's actual result which should be 01/01/2003

When you are looking at the spreadsheet it looks right but on the backside
there is this number value that I can't get rid of.
 
M

marketerinco

Thanks for the link. Unfortunately, it does not help me because the only way
to import into the email system is to have mm/dd/yyyy as the formatted data.
I have to get rid of the "serial number"
 
F

Fred Smith

What do you mean by back end formatting?

One workaround is to convert the date to text (=Text function) before the
export. Then it will stay as formatted.
 
M

marketerinco

our email program has to be an excel file in order to import. I was told by
our provider that even though the field looks like it is formatted properly
the "value" is not correct. They recommended doing a paste special which I
have done but it is still keeping the serial number format. I know this is
confusing. I just don't know how to get these fields so that they are date
fields that can be imported.
 
D

Dallman Ross

In <[email protected]>,
marketerinco said:
Thanks for the link. Unfortunately, it does not help me
because the only way to import into the email system is to have
mm/dd/yyyy as the formatted data. I have to get rid of the
"serial number"
Click to expand...

Enter your date as '01/01/03 (with the leading apostrophe)
in your cell, then.

But frankly, I am not convinced you have a problem that
formatting alone can't fix.

======================================
 
R

Ron Rosenfeld

I have a problem that I have been working on for days. I need my date fields
to read Ex: 08/01/2006 instead of 8/01/2006. I went into format field and
selected a custom format and was able to change it to the correct format.
When I save it and try to import it into an email program I get a message
that it's not formatted properly because it is seeing the backend formatting
which has the number equivilant.

Is there any way to cut and past into a new document and only take the final
outcome not the format? I have spent about 3 hours today on this and by
morning my client will be a little upset that this isn't done.

help!!! Thanks -
Click to expand...

Depending on how the data is getting into your email program, you will need to
convert the date into a text string.

For example, if your date is in Sheet1!A1 and your new document is Sheet2 then:

Sheet2!A1: =TEXT(Sheet1!a1,"mm/dd/yyyy")

If that doesn't work by itself, then select Sheet2!A1 and Edit/Copy, then
Edit/Paste Special Values


--ron
 
Top