Oversizing Cells

[Mike Connell]

Goin' to the well again.

Does anyone know how to force the visibility of the cell contents when the contents tend to oversize the column width ?

Example - I want to assign a Date and Time to a cell, but I just want to have the date visible in the cell, while the full date and time is visible in the formula bar. When the cell is "overrun", ####### are the only thing that appear.

Thanx in advance.

 

[Andrew Poulsom]

Have you tried Format|Column|Autofit?

 

[Mike Connell]

Andrew, thanx for the quick response.

I don't want the column to autofit, as that would show the time as well in the cell. I want visibility of only that portion of the cell that has the date inside the cell, even though in the formula bar there would be the date & time.

I need this "column restriction" with partial cell visibility as I am very restricted in my column widths, and I don't want the cell to show #####.


MJC

 

[Norie]

Why not just format the cell to only display the date?

 

[dcardno]

as that would show the time as well in the cell.

If you format the cell as "yyyy-mmm-dd" (or whatever date format you prefer) it will not show the time, but you will still have to make the column wide enough to show that information.

As an alternative, if you place the value in a cell -say B2- and format it as white-on-white (or set the format as ";;;") so it won't show up you could use a formula like

=text(B2,"YY-MMM-DD")

-since in Excel's mind this is a text string it will run into the next cell if it is blank (and show a partial result otherwise) instead of displaying "####" - but to see the time referenced in the formula bar a user would have to go to the cell that contains the full value - ie B2.

 

[Mike Connell]

Norie, I was hoping to capture the time as well as the date so I could possbily do some things with the time later. But I just want the date to show in the cell.

dcardno, I was hoping not to expand my logic into different cells, however, this might be an option. What I really want is to "force" the visibility of the partial contents of a cell.

Thanx.

 

[GlennUK]

Formatting a cell as a date does not destroy the time portion. Norie is right.

 

[dcardno]

Mike, the only other thing that occurs to me, if yoiu are absolutely constrained in cell widths, so that -say- 24-Mar-06 won't show up, is to format the cell in question and the cell(s) to the right as "center across selection" - don't use "merge and center;" it can create real problems with copying cells or pasting cell values or formulas.

This will allow "wider" return values to be displayed, since Excel will have two (or if you are really constrained, three or more) cells to display the result. The value, including the time component, will still be easily visible in a single cell. The only drawback is that the value will be centred across two or more cells, so the alignment may not look quite right.

 

[Mike Connell]

dcardno,

I am trying to have data entry cells with the dates in a row across nearly the whole spreadsheet (upto column IQ). I am plotting a chart over time (years) so I still can't see a solution.

Glenn & Norie,

Maybe I am just not getting what you are saying, what I need is a format that will show the date only in the cell (and not ##### when column is undersized), and show the date and time in the formular bar when the cell is selected.

Thanx for all the input.

 

[Norie]

Mike

All a format does is change how the value is displayed.

It does not alter the value, so if you have a date and time in the cell then the date and time will be displayed in the formula bar.