Oversizing Cells

Mike Connell

New Member
Joined
May 1, 2006
Messages
49
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Mike Connell

New Member
Joined
May 1, 2006
Messages
49
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Why not just format the cell to only display the date?
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Code:
=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

New Member
Joined
May 1, 2006
Messages
49
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

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

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
May 1, 2006
Messages
49
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top