Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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: | |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to add dates. | Excel Worksheet Functions | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
How do I get the dates on an excel chart to stay as dates instead. | Charts and Charting in Excel | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |