×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Probelm with Excel Tools->View->Formulas option and Time format

Probelm with Excel Tools->View->Formulas option and Time format

Probelm with Excel Tools->View->Formulas option and Time format

(OP)
  I have a worksheet containing  a column with time in HH:MM:SS format and formulas in other columns. I want to print the worksheet with formulas for which i did Options->View->Formulas.

  However, doing this changes my HH:MM:SS format to some number that i dont want. when i turn off the  view formulas option, the time again changes back to HH:MM:SS format but this doesn't show the formulas in other cells.

So my question is is it possible to view formulas in certain cells but not apply this to other cells in a worksheet?

For example consider this

       A         B                C  

1    13:24:55        25        = B1*365+(B1/25)^2
2
3


when i choose view formulas this becomes


1     0.558969907407407  25         = B1*365+(B1/25)^2


However I want A1 to be 13:24:55 but C1 to show the formula



Any suggestions for doing this?


Thanks,
Nodal DOF

RE: Probelm with Excel Tools->View->Formulas option and Time format

Copy the formula and insert a " before the = sign to make a copy of the formula as text

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Probelm with Excel Tools->View->Formulas option and Time format

(OP)
   IRstuff and joerd, Thanks for your replies. I dont have a = sign before my time stamp so i cannot conver them to text by replacing = with '=. What makes me wonder is that, though these are not formulas (No '=' sign before them), excel converts them to some decimals numbers when i select view formulas.

  Anyway, since i have only one column with  time stamp, i managed to convert them to text using cut and pasting with text import wizard. These files huge about 400mb each and have enormous data in them. So i didn't want to include any macros that may take long times to change.

Nodal DOF

RE: Probelm with Excel Tools->View->Formulas option and Time format

Time data is stored as a number in Excel, so that is what you see when the cell is not formatted as date/time, or when you view formulas (read http://www.cpearson.com/excel/datetime.htm if you're interested).
IRStuff was referring to what you have in the C-column, to see the formula in "normal" view, not in "formula" view.

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Probelm with Excel Tools->View->Formulas option and Time format

(OP)
Oh sorry. I think i confused you with my example. The C-column will be a number initially not a formula as shown in my post.

Thanks,
Nodal DOF

RE: Probelm with Excel Tools->View->Formulas option and Time format

Hi nodalDOF:

In the following illustration, I formatted cell A5 as TEXT before entering TIME to enable the value to be showed in hh:mm:ss display even when the formulas are being displayed ...

thread770-200129: Probelm with Excel Tools->View->Formulas option and Time format.gif" border="0">

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

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!


Resources