Cells keep insisitng on showing as dates

[therock003]

I have set a table to store some data and in one column i store proeuct codes. Weirdly enough the formar code is XX-XX-XX where xx is a number from 00-99. As you can imagine some get recognised as dates by excel. For example 35-42-67 is not an acceptable input for date, but lets say an item had the product code of 10-10-03 which coincidentally is the date equivalent of 10 october of 2003. I have of course gone to cell formatring and turn this entire column to text input or other formata but still the exclamation pops up that its a suspicious entry and thus when i sort this column everything gets messed up. Have you got any solutions for this

 

[kvsrinivasamurthy]

when i sort this column everything gets messed up

Not able to follow what happens. Update the image before an after sort

For exclamation pops up Select all cells and ignore error.

 

[jasonb75]

There is an option that you can change which will stop this happening, but it will apply to excel as a whole, not just that specific column.

File > Options > Formulas > Error checking rules. Uncheck 'Cells containing years represented as 2 digits'

Another option would be to use a custom date format that matched the product code format, but that would likely cause problems elsewhere, especially with the sorting and any formulas that refer to the codes.

edit:-

If the cells are pre-formatted as text then it should not affect sorting, but you will still see the error warnings.

 

[MARK858]

For exclamation pops up Select all cells and ignore error.

Just to say that you have to ignore the error on the first cell in the selection to clear the error indicator on all the cells. If you ignore the error on any other cell it will just clear it from that cell.

 

[therock003]

Thanx for your replies. Guys as i said i use the xx-xx-xx format for item codes. I unchecked the cells represented by last two digits and error dissapeared. But i still have problems with sorting.

Welp in order to further explaining. I need to sort my tables based on code number so when a client asks for a product, the eye gets directly on top of things, instead of searching and control effing all over the place. So i need to sort from lower to higher. So.... Lets say i have 4 codes

21-05-01, 16-05-01, 15-75-04 and 10-40-08. So if i sort alphabetically the list will become

10-40-08, 15-75-04, 16-05-01 and 21-05-01

but at the moment it lists them as:

16-05-01, 21-05-01, 10-40-08 and 15-75-04

So as you can see, it detects 16-05-01 and 21-05-01 as dates and it prioritises them in sorting. Although ive clearly set their formating as text ,and unchecked the error checking for date format

 

[jasonb75]

Formatted as text and sorted in ascending order, this is what I get.

Book1
	A
1	Code
2	10-40-08
3	15-75-04
4	16-05-01
5	21-05-01
Sheet1

I've tried sorting A-Z and Z-A, both with normal ranges and tables. Everything is sorting correctly, not sure why yours is different. Only thing that comes to mind is that it is sorting by the wrong column if you are trying to sort a table.

 

[therock003]

Formatted as text and sorted in ascending order, this is what I get.

Book1
	A
1	Code
2	10-40-08
3	15-75-04
4	16-05-01
5	21-05-01
Sheet1

I've tried sorting A-Z and Z-A, both with normal ranges and tables. Everything is sorting correctly, not sure why yours is different. Only thing that comes to mind is that it is sorting by the wrong column if you are trying to sort a table.

Yes it is on a table

 

[therock003]

Yes it is on a table

Why does table sorting work differently though?

 

[jasonb75]

For the reason that I said maybe?

Only thing that comes to mind is that it is sorting by the wrong column if you are trying to sort a table.

In this case I was referring to data that looks like a table rather than an actual data table. If you select say 4 columns of data, then click the sort arrow then it might not be sorting by the column that you want it to.

I have not been able to make it sort incorrectly when all of the codes are formatted as text so I'm trying to think of reasons why yours might behave differently.

 

[therock003]

For the reason that I said maybe?

In this case I was referring to data that looks like a table rather than an actual data table. If you select say 4 columns of data, then click the sort arrow then it might not be sorting by the column that you want it to.

I have not been able to make it sort incorrectly when all of the codes are formatted as text so I'm trying to think of reasons why yours might behave differently.

No im using an actual data srt formatted as a tablr and im sorting by selecting the desired column. Its so weird to me that im not getting the desired effect