converting date to number
 

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

converting date to number
 

Perhaps this formula in another column

=TEXT(A1,"yyyy-mm-dd")

(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,"yyyy-mm-dd"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513817

converting date to number
 

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

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

"Todd" wrote:

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

converting date to number
 

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.

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

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

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

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.

"LynneH10" wrote:

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.

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

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

Pete

LynneH10 wrote:
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.

"LynneH10" wrote:

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.

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

Pete, you are a jewel! Thanks so much, it worked!

Lynne

"Pete_UK" wrote:

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

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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

converting date to number
 

Thanks for the feedback, Lynne.

Pete

LynneH10 wrote:
Pete, you are a jewel! Thanks so much, it worked!

Lynne

"Pete_UK" wrote:

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

"Kevin Vaughn" wrote:

If 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,"yyyy") & "-" & TEXT(A2,"mm") & "-" & TEXT(A2,"dd")
You could then do copy-paste special values and format as text.

HTH
--
Kevin Vaughn


"Todd" wrote:

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