software

I exported data from a scheduling program to an excel spreadsheet and data is
now formatted as as date instead of a number. What was 4631-01-10 is now
1/10/4631 and if I convert it to text becomes 997488. How do I convert the
data back into its origonal format? (4631-01-10)

Thanks,

Todd
Perhaps this formula in another column

=TEXT(A1,quot;yyyy-mm-ddquot;)

(assuming your regional settings are for US type date, i.e. mm/dd/yy)

If you have some entries which have been converted to date and some not
try

=IF(ISTEXT(A1),A1,TEXT(A1,quot;yyyy-mm-ddquot;))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=513817If you still have the original data and it was a csv file, you might want to
change the extension to .txt where you should be given the option of
formatting that data as text. If not, with the cells formatted as text, try
this in a helper column

=TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughnquot;Toddquot; wrote:

gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; data back into its origonal format? (4631-01-10)
gt;
gt; Thanks,
gt;
gt; Todd
gt;

What I've done is copied the whole column into word, then by using the find
and replace function i've changed it to something else that excel will not
recognize as a date seperator ie. a space. Then Paste it back.

Arnoldo

quot;Toddquot; wrote:

gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; data back into its origonal format? (4631-01-10)
gt;
gt; Thanks,
gt;
gt; Todd
gt;

I have having difficulty with formatting cells also. I want to use 9/06 for
September 2006 and there is no formatting to be able to achieve this. It just
makes it September 6, 2006. I want 9/06. I have tried everything it it does
no good.

quot;Kevin Vaughnquot; wrote:

gt; If you still have the original data and it was a csv file, you might want to
gt; change the extension to .txt where you should be given the option of
gt; formatting that data as text. If not, with the cells formatted as text, try
gt; this in a helper column
gt;
gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; You could then do copy-paste special values and format as text.
gt;
gt; HTH
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Toddquot; wrote:
gt;
gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; data back into its origonal format? (4631-01-10)
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Todd
gt; gt;

Hi Lynne,

select one of the cells with a date in it and click Format | Cells |
Number (tab) and select Custom (at the bottom of the list). You will
then be presented with a scrollable list of pre-defined custom formats,
but I don't think the one you want will be in it, so enter this
directly into the panel:

m/yy

and click OK. If this gives you what you want you can apply it to other
cells by using the Format Painter - select this cell and click the
Format Painter icon (next to Paste icon), then select the cell(s) that
you want the format to apply to.

Hope this helps.

Pete

LynneH10 wrote:
gt; I have having difficulty with formatting cells also. I want to use 9/06 for
gt; September 2006 and there is no formatting to be able to achieve this. It just
gt; makes it September 6, 2006. I want 9/06. I have tried everything it it does
gt; no good.
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; If you still have the original data and it was a csv file, you might want to
gt; gt; change the extension to .txt where you should be given the option of
gt; gt; formatting that data as text. If not, with the cells formatted as text, try
gt; gt; this in a helper column
gt; gt;
gt; gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; gt; You could then do copy-paste special values and format as text.
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Toddquot; wrote:
gt; gt;
gt; gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; gt; data back into its origonal format? (4631-01-10)
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; Todd
gt; gt; gt;The cell I want to format is my original file of address' for people to get a
newsletter. Nothing was imported. Just plain Excel 2003 database I created.
Thanks for any ideas other than Microsoft programming it into the next
version.

quot;LynneH10quot; wrote:

gt; I have having difficulty with formatting cells also. I want to use 9/06 for
gt; September 2006 and there is no formatting to be able to achieve this. It just
gt; makes it September 6, 2006. I want 9/06. I have tried everything it it does
gt; no good.
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; If you still have the original data and it was a csv file, you might want to
gt; gt; change the extension to .txt where you should be given the option of
gt; gt; formatting that data as text. If not, with the cells formatted as text, try
gt; gt; this in a helper column
gt; gt;
gt; gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; gt; You could then do copy-paste special values and format as text.
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;Toddquot; wrote:
gt; gt;
gt; gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; gt; data back into its origonal format? (4631-01-10)
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; Todd
gt; gt; gt;

Did you not see my response, posted about 3 hours ago?

Pete

LynneH10 wrote:
gt; The cell I want to format is my original file of address' for people to get a
gt; newsletter. Nothing was imported. Just plain Excel 2003 database I created.
gt; Thanks for any ideas other than Microsoft programming it into the next
gt; version.
gt;
gt; quot;LynneH10quot; wrote:
gt;
gt; gt; I have having difficulty with formatting cells also. I want to use 9/06 for
gt; gt; September 2006 and there is no formatting to be able to achieve this. It just
gt; gt; makes it September 6, 2006. I want 9/06. I have tried everything it it does
gt; gt; no good.
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; If you still have the original data and it was a csv file, you might want to
gt; gt; gt; change the extension to .txt where you should be given the option of
gt; gt; gt; formatting that data as text. If not, with the cells formatted as text, try
gt; gt; gt; this in a helper column
gt; gt; gt;
gt; gt; gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; gt; gt; You could then do copy-paste special values and format as text.
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; gt; gt; data back into its origonal format? (4631-01-10)
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt;
gt; gt; gt; gt; Todd
gt; gt; gt; gt;Pete, you are a jewel! Thanks so much, it worked!

Lynne

quot;Pete_UKquot; wrote:

gt; Hi Lynne,
gt;
gt; select one of the cells with a date in it and click Format | Cells |
gt; Number (tab) and select Custom (at the bottom of the list). You will
gt; then be presented with a scrollable list of pre-defined custom formats,
gt; but I don't think the one you want will be in it, so enter this
gt; directly into the panel:
gt;
gt; m/yy
gt;
gt; and click OK. If this gives you what you want you can apply it to other
gt; cells by using the Format Painter - select this cell and click the
gt; Format Painter icon (next to Paste icon), then select the cell(s) that
gt; you want the format to apply to.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt; LynneH10 wrote:
gt; gt; I have having difficulty with formatting cells also. I want to use 9/06 for
gt; gt; September 2006 and there is no formatting to be able to achieve this. It just
gt; gt; makes it September 6, 2006. I want 9/06. I have tried everything it it does
gt; gt; no good.
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; If you still have the original data and it was a csv file, you might want to
gt; gt; gt; change the extension to .txt where you should be given the option of
gt; gt; gt; formatting that data as text. If not, with the cells formatted as text, try
gt; gt; gt; this in a helper column
gt; gt; gt;
gt; gt; gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; gt; gt; You could then do copy-paste special values and format as text.
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; gt; gt; data back into its origonal format? (4631-01-10)
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt;
gt; gt; gt; gt; Todd
gt; gt; gt; gt;
gt;
gt;

Thanks for the feedback, Lynne.

Pete

LynneH10 wrote:
gt; Pete, you are a jewel! Thanks so much, it worked!
gt;
gt; Lynne
gt;
gt; quot;Pete_UKquot; wrote:
gt;
gt; gt; Hi Lynne,
gt; gt;
gt; gt; select one of the cells with a date in it and click Format | Cells |
gt; gt; Number (tab) and select Custom (at the bottom of the list). You will
gt; gt; then be presented with a scrollable list of pre-defined custom formats,
gt; gt; but I don't think the one you want will be in it, so enter this
gt; gt; directly into the panel:
gt; gt;
gt; gt; m/yy
gt; gt;
gt; gt; and click OK. If this gives you what you want you can apply it to other
gt; gt; cells by using the Format Painter - select this cell and click the
gt; gt; Format Painter icon (next to Paste icon), then select the cell(s) that
gt; gt; you want the format to apply to.
gt; gt;
gt; gt; Hope this helps.
gt; gt;
gt; gt; Pete
gt; gt;
gt; gt; LynneH10 wrote:
gt; gt; gt; I have having difficulty with formatting cells also. I want to use 9/06 for
gt; gt; gt; September 2006 and there is no formatting to be able to achieve this. It just
gt; gt; gt; makes it September 6, 2006. I want 9/06. I have tried everything it it does
gt; gt; gt; no good.
gt; gt; gt;
gt; gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If you still have the original data and it was a csv file, you might want to
gt; gt; gt; gt; change the extension to .txt where you should be given the option of
gt; gt; gt; gt; formatting that data as text. If not, with the cells formatted as text, try
gt; gt; gt; gt; this in a helper column
gt; gt; gt; gt;
gt; gt; gt; gt; =TEXT(A2,quot;yyyyquot;) amp; quot;-quot; amp; TEXT(A2,quot;mmquot;) amp; quot;-quot; amp; TEXT(A2,quot;ddquot;)
gt; gt; gt; gt; You could then do copy-paste special values and format as text.
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt; --
gt; gt; gt; gt; Kevin Vaughn
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I exported data from a scheduling program to an excel spreadsheet and data is
gt; gt; gt; gt; gt; now formatted as as date instead of a number. What was 4631-01-10 is now
gt; gt; gt; gt; gt; 1/10/4631 and if I convert it to text becomes 997488. How do I convert the
gt; gt; gt; gt; gt; data back into its origonal format? (4631-01-10)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Todd
gt; gt; gt; gt; gt;
gt; gt;
gt; gt;