×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting Dates To Real Integers

Converting Dates To Real Integers

Converting Dates To Real Integers

(OP)
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!!

RE: Converting Dates To Real Integers

I think you are going to kick yourself.

type =3/16

Cheers

Greg Locock

RE: Converting Dates To Real Integers

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

RE: Converting Dates To Real Integers

(OP)
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.

RE: Converting Dates To Real Integers

(OP)
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!!

RE: Converting Dates To Real Integers

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

RE: Converting Dates To Real Integers

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

RE: Converting Dates To Real Integers

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close