Converting Dates To Real Integers

[shearstrength]

Using Excel 2000;

I am opening a HTML file into excel 2000. This HTML file contains a table made up of cells.

I am trying to convert a cell that seems to be programmed to display always the date and there is no way for me to change this setting within the Excel "Cell Format".

example;

3/16 in the cell is displayed as 16-Mar and displayed as 3/16/2002 in the "Formula Bar". When I change the setting of the cell to display it as a number, the 3/16 becomes 37331, which is still programmed to display as dates. I read that a conversion equation is needed in order to convert 3/16 to actually be 0.1875, and not 16-Mar or 37331.

Does any one have any idea on how to resolve this problem?

Any response will be greatly appreciated!!

 

[GregLocock]

I think you are going to kick yourself.

type =3/16

Cheers

Greg Locock

 

[dison]

I think I know what you are having trouble with. When you open the HTML file it is automatically making the number 03/16 show up as a date (which in excel is a serial number 37331 if the format is changed from date). Two possible solutions:

1) When you open the file (at least with text files) a wizard opens up to help you determine the format you want it opened in. For example, how to delineate the break for information in cells and what format (text, number, date, etc.) you would like each column to be. Carefully go through each column and choose number or text instead of date. This should solve your problem unless excel is not letting you choose how you want the format upon opening the file. Then the second solution should work for you.

2) Because excel made this a serial number in the first place, you can make it give you the information you actually wanted. I assume that the information you want is either 3/16=0.1875 or the text 3/16. Say that the serial number is in cell A1. First, you need to use the function =MONTH(A1) to give you the result in cell B1 of 3. Then use the function =DAY(A1) to give the result of 16 in cell C1. Then simply use a formula =B1/C1 to get the result 0.1875 or =B1&"/"&C1 to get the text 3/16. OR you can do it all in one cell by the following =MONTH(A1)/DAY(A1).

Hope this helps - Dison

 

[shearstrength]

That does work, but I have to set the cell to "Fractions", then manually type each cell over again and then set the cell to "numbers" to 3 or 4 decimal places.

There are 155 cells to change, so you can see that this method is very time consuming.

I would like Excel 2000 to automatically convert the date to a real integer without having to go through this very long process.

 

[shearstrength]

Dison, your second option works perfectly.

I would like to know though, can I replace the existing cell (the cell containing the date) with the formula and the converted value, or must I place the formula and converted value in a seperate cell?

Thanks for your help, my task is now a very simple one!!

 

[dison]

shearstrength,

I'm sorry, but I don't understand your problem. Why must you set the cell to fractions? Why manually type the formula in each cell?

If you type the formula =MONTH(cellnamehere)/DAY(cellnamehere) next to the imported date cell, you should get the numerical result in decimal form. Click on format cells number and change number of decimal places if required. Once you have this formula in the first cell, simply fill down (or to the side) to copy the formula to the remaining cells, which should then calculate this result for all imported date cells.

~dison

 

[dison]

shearstrength,

I don't think you will be able to replace the original cell because the value of that cell is being used to calculate the new result that you need. However, once you have the results, you can move the original cells away to a different column out of your way and then move the result to the vacated column (or delete the vacated column).

~dison

 

[melone]

Just do a "Paste Special" and choose "Values"