Dates won't stay formated
 

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 -

Dates won't stay formated
 

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

Dates won't stay formated
 

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:


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

Dates won't stay formated
 

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


"marketerinco" 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
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 -

Dates won't stay formated
 

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

"Rookie 1st class" wrote:

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


"marketerinco" 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
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 -

Dates won't stay formated
 

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Dates won't stay formated
 

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.

"Niek Otten" wrote:

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Dates won't stay formated
 

37622 is the serial number of the date.

That's how Excel stores dates.

See Chip Pearson's site for info on serial dates.

http://www.cpearson.com/excel/datetime.htm#SerialDates

Perhaps you can manipulate the field format in your email program?


Gord Dibben MS Excel MVP

On Sun, 12 Nov 2006 15:36:02 -0800, marketerinco
wrote:

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.

"Niek Otten" wrote:

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Dates won't stay formated
 

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"

"Gord Dibben" wrote:

37622 is the serial number of the date.

That's how Excel stores dates.

See Chip Pearson's site for info on serial dates.

http://www.cpearson.com/excel/datetime.htm#SerialDates

Perhaps you can manipulate the field format in your email program?


Gord Dibben MS Excel MVP

On Sun, 12 Nov 2006 15:36:02 -0800, marketerinco
wrote:

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.

"Niek Otten" wrote:

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

Dates won't stay formated
 

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.

--
Regards,
Fred


"marketerinco" wrote in message
...
No luck. It still will not import. It is still seeing the back end
formatting that Excel does to the field.

"Rookie 1st class" wrote:

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


"marketerinco" 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
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 -

Dates won't stay formated
 

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.

"Fred Smith" wrote:

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.

--
Regards,
Fred


"marketerinco" wrote in message
...
No luck. It still will not import. It is still seeing the back end
formatting that Excel does to the field.

"Rookie 1st class" wrote:

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


"marketerinco" 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
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 -

Dates won't stay formated
 

In ,
marketerinco spake thusly:

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"

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.

======================================
"Gord Dibben" wrote:

37622 is the serial number of the date.

That's how Excel stores dates.

See Chip Pearson's site for info on serial dates.

http://www.cpearson.com/excel/datetime.htm#SerialDates

Perhaps you can manipulate the field format in your email
program?

Gord Dibben MS Excel MVP

On Sun, 12 Nov 2006 15:36:02 -0800, marketerinco
wrote:

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.

"Niek Otten" wrote:

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

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

-- Kind regards,

Niek Otten Microsoft MVP - Excel

"marketerinco"
wrote in message
...

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

Dates won't stay formated
 

On Sun, 12 Nov 2006 11:13:01 -0800, marketerinco
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
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 -

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