How to turn dates back to numbers

**blockrocker** · 08-16-2011, 04:52 PM

Hi!

I opened a html page to excel that contained tables of numbers.

The import went otherwise fine, but some of the numbers got turned into dates.

When I try to format those cells to be fractions again, I get crazy numbers like 40586 which is obviously not what the number was originally.

How can I get excel to display the original numbers instead of dates?

**shg** · 08-16-2011, 05:08 PM

Try pre-formatting the cells as Fraction before you import, and try again.

**blockrocker** · 08-16-2011, 05:14 PM

Originally Posted by shg

Try pre-formatting the cells as Fraction before you import, and try again.

I already tried that, but it doesn't seem to work since I use "open file" command to get the html-document into excel.

I'm also very curious to find out if it is not possible to convert the numbers back to the original ones, since IMHO that would be a huge missing feature in such a big program like Excel.

**ChemistB** · 08-16-2011, 05:21 PM

It is definitely not possible once Excel has converted them to dates. Sorry.

**blockrocker** · 08-16-2011, 05:32 PM

Originally Posted by ChemistB

It is definitely not possible once Excel has converted them to dates. Sorry.

Why oh why!

You would think it would be the easiest thing in the world since if you can convert a number like 21,5 into a date like 21.may then why on earth can you not do the same process backwards in Excel? :S

**shg** · 08-16-2011, 05:38 PM

Provide an exhaustive list of all the fractional data string formats that have been converted to dates.

**ChemistB** · 08-16-2011, 05:43 PM

Hmmm, let's say your imported area is A1:V25 in sheet1
In Sheet2, format that same range as fraction. Then in Sheet2!A1

=MONTH(Sheet1!A1)/DAY(Sheet1!A1)
It will simplify your fractions (i.e. 3/15 will become 1/5) but it should work.

EDIT, depending on your default date format
=DAY(Sheet1!A1)/MONTH(Sheet1!A1)