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

[nodalDOF]

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

 

[IRstuff]

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

TTFN

FAQ731-376

 

[joerd]

You might want to look at the XLC addin (

http://www.excelcalcs.com/)

or a "display formulas" user-defined function - search for it in this forum and faq770-79.

Cheers,
Joerd

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

 

[nodalDOF]

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

 

[joerd]

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 for tips on how to make the best use of Eng-Tips.

 

[nodalDOF]

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

 

[yogia]

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 ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com