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

The original title: "10 times faster than your stupid method, this is the best way to organize date data in Excel!" "

In our daily work, we often deal with date data in addition to finding, summing, and so on.

The functions we often use are Date (merge date), Year (year to get date), Month (month to get date), Day (number of days to get date), and so on.

This is not, recently a friend sent a data, which is derived from the system. As shown below:

The system automatically mixes the date with the model, and now needs to extract the date.

The extraction rules are:

The first three characters of ❶ represent the number of months.

The 5th and 6th characters of ❷ represent two-digit years.

The 7th and 8th characters of ❸ represent two-digit days.

The extraction result is as follows:

That is, the date that converts the English expression [month-year-day] into a purely numerical [year-month-day] form.

Problem analysis most of the dates we usually deal with are made up of pure numbers, and we may have little contact with this kind of English dates.

But once we meet, we still have to find a way to solve it, right?

For example, in the case mentioned earlier, we can use the following function to extract the date in the form of [year-month-day].

= DATE (MID (A2Magne5), LEFT (A2Magne3), MID (A2Magne7)

It turns out to be the wrong value!

Why? Just look down and you'll see!

First, let's take a look at the analysis of the formula:

❶, let's first extract the number of years by using MID (A2re5 ~ 2).

Starting with the fifth character of the text, intercept 2 characters, and the result is as follows:

The number of months is extracted by ❷ and then using LEFT (A2Magol 3).

Starting with the first character of the text, intercept 3 characters, and the result is as follows:

After ❸, the number of days is extracted by using MID (A2Power7).

Starting with the 7th character of the text, intercept 2 characters:

❹ finally integrates [year, month and day] with the Date function.

= DATE (MID (A2Magne5), LEFT (A2Magne3), MID (A2Magne7)

Doesn't it look like a problem? Why does the error value # VALUE occur?

In fact, the problem is that the month is the English alphabet. Not numbers.

The Date function requires that all three parameters must be numeric. (whether it is text or numeric)

Now that we have a train of thought, let's see how to solve it.

Solution A converts English months into numbers, and we can use the Match function.

The formula is as follows:

= MATCH ("Mar", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0) Formula Analysis:

MATCH (lookup_value, lookup_array, [match_type])

The function takes three parameters:

❶ look up the value.

First parameter: this is the month we are looking for here, for example: "Mar"

Which region or array does the ❷ look in?

Second parameter: we need to construct a January-December memory array:

{"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}

Use the Match function to find the position of the first parameter in the second parameter. If it is in the third parameter, it returns 3, which also means March.

❸ exact or fuzzy matching.

The third parameter: enter 0 to indicate exact search.

Finally, we integrate the above formula together, and the result is:

The formula is as follows:

= DATE (MID (A2Power3), MATCH (LEFT (A2Power3), "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec", 0), the second part of MID (A2Magne7) consists of Match function. Returns the numeric month.

So the result comes out!

However, did our friends find that the year was wrong? Excel automatically added 19 numbers to us.

This is not what we want, so we can add 20 before the year.

The formula is as follows:

= DATE (20&MID (A2Power3), MATCH (LEFT (A2Power3), {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0) if friends do not want to enter the constant array of months manually, you can also use the following formula:

The formula is as follows:

= DATE (20&MID (A2Power5), MATCH (LEFT (A2Magne3), TEXT (20&-ROW ($1RZ 12), "mmm"), 0), MID (A2Magne7)), where:

TEXT (20&-ROW ($1 mmm 12)) constructs an array of constants for English months. The result of this formula happens to be the English month array above.

Interested friends can refer to it.

Option B in addition, in addition to the routine above, there is a very simple method, that is, the following method.

In [format cells] = "[number] =" [date], view the display of built-in English dates.

As can be seen from the picture:

The English date is displayed as follows: [day-month-year]

In this way, we can combine it into a date form, and then convert it to a target date. As shown below:

[B2] the formula is as follows:

= MID (A2Magne7) & "-" & LEFT (A2Pol 6) Formula Analysis:

❶ first extracts the number of days with MID (A2Power7): 12

❷ then uses LEFT (A2JE6) to extract the month and year: Mar-22

❸ finally uses the & symbol to connect a date symbol "-": 12-Mar-22

The date style has been combined into the system's built-in date form.

But the question now is how to convert to a purely numeric target date?

In fact, the method is very simple!

Just do a mathematical operation! As shown below:

[B2] the formula is as follows:

=-(MID (A2Magne7) 2) & "-" & LEFT (A2Power6)) Formula parsing:

Put a pair of parentheses () on the outside of the original formula, and then use two negative signs (- -) to convert it to a pure numeric date.

Such a simple transformation to achieve our needs, or quite simple!

At the end of today, we share how to convert English dates into pure digital dates that conform to Chinese habits.

❶ uses the usual methods of date and text interception functions to convert [month-year-day] to [year-month-day].

The ❷ simulation applies the built-in style of the system for conversion.

It can be seen that using the second method, not only the formula is very simple, but also greatly improve the efficiency of the table.

I hope that in the usual work, you can pay more attention to the various settings built into the system, and you will certainly find a lot of useful functions!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.