Shulou(Shulou.com)11/24 Report--

Date is the indispensable and most special data in Excel table. If you can control it, you can run the watch like a fly; if you can't control it, it can make you feel bad.

Well, another cousin touched the dust.

1. Cousin Xiaofang: I want to extract "month" and "day" from the date data, but why don't the "month" and "day" functions work?

If it were you, how would you help this cousin?

2. The easiest way to achieve immediate results is, of course, the sorting method.

Is that the end of it? However, it is not!

Indeed, this method can meet the needs put forward by Xiaofang-"extracting months and days". However, this is only a stop-gap method.

Why?

Because the fundamental problem is that the date of the data source is not standardized.

3. Three tricks to solve the problem thoroughly, or is it recommended to use the following three tricks to organize and standardize the date and convert it into a standard date:

❶ fractional transformation method

Using the feature that the sorting wizard can preset the exported format, it is a common practice to force the conversion of the data format.

❷ substitution conversion method

For the data with obvious rules, the replacement method can also be used to remove redundant characters in batches, so as to achieve the purpose of conversion.

❸ function transformation method

The function formula is more complex, and it is suitable for the situation where the data needs to be updated frequently and calculated automatically.

4. Break the casserole and ask to the end in most cases, we want to get the month and day information separately in order to facilitate screening, statistics and analysis.

However, this is a pseudo-demand.

If we keep asking why, we can find the crux of the problem:

Why extract month and day? Is that necessary?

Why can't you use functions? What's the problem?

Because the date is a special number, it itself is made up of three parts: year, month and day, and it is the real date of "living".

If it conforms to the standard format, Excel can automatically recognize each of these parts. It needs to be screened and analyzed by year, month and day, and it does not need to be extracted separately at all.

For example, when you do data filtering, the filtering conditions will automatically become a layer-by-layer structure, which is very convenient.

Standard date for automatic grading display

However, a lot of date data derived from other systems and software, although it looks like a standard date, is actually a false date of "death". Excel can not automatically identify its year, month, day and other parts. Think of it as a whole string of text, whether it's filtering, data perspective, or function formula:

False dates that cannot be graded automatically

Therefore, only when the text-based false date is completely converted into the digital real date, can we simplify the complexity and do it once and for all. Even if we continue to extract the information of the year, month and day for other calculations, it is also easy.

❶ fractional transformation method

❷ substitution conversion method

❸ function formula method

So, are you Get here yet?

Take care of Excel, work overtime and stay away from me! Do you also have Excel problems? Let's let go and chat in the message area.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Akiba, editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.