Why will a text field flow into the next cell, but date field will not?

[JenniferMurphy]

This minisheet shows that the date fields in B4, D4, & F4 will not flow into the cells to the right, but the text fields in the cells above will. Is there any way I can get them both to flow?

2022 Power Rankings.xlsx
	B	C	D	E	F
3	Week 2		Week 1		Week 0
4	####		####		####
5	Wk 2 Rank	Wk 2 Δ	Wk 1 Rank	Wk 1 Δ	Wk 0 Rank
6	3	-1	2	-1	1
7	2	-1	1	+1	2
8	1	+2	3	=0	3
9	4	=0	4	=0	4
Sheet1

Cell Formulas
Range	Formula
C6:C9	C6	=IF(ISNUMBER([@[Wk 2 Rank]]),[@[Wk 1 Rank]]-[@[Wk 2 Rank]],"--")
E6:E9	E6	=IF(ISNUMBER([@[Wk 1 Rank]]),[@[Wk 0 Rank]]-[@[Wk 1 Rank]],"--")

If I widen the columns, I can ser all the data.

2022 Power Rankings.xlsx
	B	C	D	E	F
3	Week 2		Week 1		Week 0
4	9/13/22		9/06/22		8/09/22
5	Wk 2 Rank	Wk 2 Δ	Wk 1 Rank	Wk 1 Δ	Wk 0 Rank
6	3	-1	2	-1	1
7	2	-1	1	+1	2
8	1	+2	3	=0	3
9	4	=0	4	=0	4
Sheet1

Cell Formulas
Range	Formula
C6:C9	C6	=IF(ISNUMBER([@[Wk 2 Rank]]),[@[Wk 1 Rank]]-[@[Wk 2 Rank]],"--")
E6:E9	E6	=IF(ISNUMBER([@[Wk 1 Rank]]),[@[Wk 0 Rank]]-[@[Wk 1 Rank]],"--")

 

[Micron]

That is the application's way of handling values that exceed the width of the cell. There are other 'rules' pertaining to merged cells, wrap text options and such. I believe text spills, longs become scientific notation and dates become ### (which by the way, is the delimiter for date data type, so that makes sense). If there's a way to make dates spill from settings, I don't know of it. Possibly could if you wrap data in Text function, but that might cause more issues than it solves.

 

[JenniferMurphy]

That is the application's way of handling values that exceed the width of the cell. There are other 'rules' pertaining to merged cells, wrap text options and such. I believe text spills, longs become scientific notation and dates become ### (which by the way, is the delimiter for date data type, so that makes sense). If there's a way to make dates spill from settings, I don't know of it. Possibly could if you wrap data in Text function, but that might cause more issues than it solves.

If it's Excel's way of handlng values that exceed the cell width, why do the text fields in Row 3 extend into the cells to the right? Why allow one type of data to flow over and not another?

 

[Micron]

why do the text fields in Row 3 extend into the cells to the right?

Because the data and formatting follow the rules.
I have no idea why and can't recall ever caring. I'd just resize the column or merge and move on. You can review this if you want to know more.

When does Excel decide to overflow text into adjacent cells, or cut them at the boundary?

In Excel 2010, or any other version probably, if I enter in a cell a long single-line text that is longer than the width of the cell, Excel sometimes render the text across the next adjacent cells;...

superuser.com

 

[JenniferMurphy]

Because the data and formatting follow the rules.

Arbitrary rules, IMNSHO

I have no idea why and can't recall ever caring.

Oh, well, I'm so sorry to have bothered you with something you don't care about. Kinda makes me wonder why you would even bother to reply to something you don't care about.

I'd just resize the column or merge and move on.

I don't want to resize the column. If I did, there wouldn't be a problem.

You can review this if you want to know more.

When does Excel decide to overflow text into adjacent cells, or cut them at the boundary?

In Excel 2010, or any other version probably, if I enter in a cell a long single-line text that is longer than the width of the cell, Excel sometimes render the text across the next adjacent cells;...

superuser.com

Thanks for the link. It sounds like the rules are prertty much arbitrary and at least a few others also have complaints about it.

 

[Alex Blakenburg]

The only way you are going to get what you want is to enter the dates as text and there are a number of options for that or if you are using a formula, to populate it using Text() to convert and format it.
Your issue applies to all numbers (which includes dates).
I don't know if it has to do with, that normally with numbers you want the right hand side to align and decimal points and commas to align.

Another option might be the Alignment Shrink to Fit, it is effective in terms of being able to read the data but not pretty.
Center across selection would also work but would be even uglier in your scenario.

 

[JenniferMurphy]

The only way you are going to get what you want is to enter the dates as text and there are a number of options for that or if you are using a formula, to populate it using Text() to convert and format it.
Your issue applies to all numbers (which includes dates).
I don't know if it has to do with, that normally with numbers you want the right hand side to align and decimal points and commas to align.

Another option might be the Alignment Shrink to Fit, it is effective in terms of being able to read the data but not pretty.
Center across selection would also work but would be even uglier in your scenario.

I ended up entering the dates as '9/13/22, etc. That works. They are literal dates in this case, not computed, so not a problem.

Thanks for the tips.

 

[Alex Blakenburg]

No problem. Glad we could help.

 

[Micron]

Kinda makes me wonder why you would even bother to reply

Don't worry, I won't make that mistake with you again. I already suggested using text but apparently that didn't go over well either.