converting date to number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,"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"))
 
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
 
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
 
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.
 
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
 
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.
 
Back
Top