Hello. There is such a thing. Why do the two date cells have different filtering implemented? Thank.

Here is

  • most likely some cells have the text format, and some dates (just applying the date format to the column does not always turn the text into a date) you can try DATAZNACH - Batanichek

2 answers 2

The reason for the above described filter behavior is that part of the cells has a date format (displayed as a hierarchical tree), and part of the cells has a text format.

Table without filters For rows of the Дата column, v values ​​in the ТипЯчейки column correspond to any other content except text ( l ) or empty cell value ( b ), in the ФорматЯчейки column, D1 value corresponds to the date format ДД.ММ.ГГГГ and G is the general format.

Filter table for 'Date' column (cells contain both numeric and text values)

To convert the Общего format to the Даты format, we use the formula =Дата*1 ( Дата*1 column Дата*1 ).

Filter table for 'Date * 1' column (cells contain only numeric values)

Description ЯЧЕЙКА function

  • >>> To convert the General format to the Date format ... - there is an inaccuracy here. If the cell has the General format, then after inserting the date, the format will either automatically be converted to the Date, or we will see a 5-digit number. But if the date is inserted as text, then the cell format is powerless, it can be General, Text, Number, Financial ... - we will see the text - vikttur

The date in the usual Excel presentation is the number: the number of days since January 1, 1900. For example:

 "31.08.2016"=42613

Excel sees dates like this - by number. We are allowed to do this (using the cell format) in the way that is convenient for us, but in fact in the cell there is a 5-digit number. This can be easily verified. We write in A1 date.

 =ПОИСК(".";A1) =ДЛСТР(A1)

The first formula will show an error (there is no dot in the value), the second will indicate that the number of characters = 5. But if the first shows the position of the point and the result of the second> 5, then we can safely say that there is no date in the cell, there is a text.

Text / non-text can still be checked like this:

 =ЕЧИСЛО(A1) =ЕТЕКСТ(A1)

Numeric data recorded in a textual representation is common.

Mathematical operations convert the text entry of a number into a numeric one. This can be done using formulas, as Roman Sidorin showed:

 =дата*1 =дата+0 =--дата

It is necessary or leave extra. column in the table, or replace the original range with the obtained data.

'---------------

Conversion can be done using built-in tools.

Copy an empty cell (or a cell with a unit), select the desired range, the Edit-Special-Grade-Fold menu (if the unit is copied, then Multiply ) -OK . If you copied a cell with a format other than Date , then change the format of the cells to the desired one.