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

The title of the original text: "which person has come up with these two Excel formulas, Ctrl+V is going to say goodbye!" "

Hello, everyone. I am studying the cool of Excel data. Today we will take a look at the relevant knowledge of date-time data format.

When the user browses the interface of the website, the system will record these times automatically.

The analysis of the stay time of website users will help us to judge the stickiness of users to the site and whether the content quality of the site still needs to be optimized.

We first export this date-time data from the system to Excel, and now we want to know how long it takes to operate between the two. What should we do?

Date and time are numeric in nature, and they can be added or subtracted directly.

However, as shown in the following figure, the date-time format exported from the system above is not standardized, so we also need to convert it to a standard date-time format in advance.

Next, let's take a look at two approaches:

① Excel function (need to use Left function, Right function, Text function).

② PowerQuery .

PS. PowerQuery (PQ for short) is a plug-in that comes with Excel2016 of Office and above. Plug-ins need to be installed in version 2013.

01, Excel function in Excel, the date format is converted to a standard date, generally, the first function we think of is the Text function, which allows cell values to be displayed in a specified format.

In this case, 20220613 17:10:19, consists of two parts, date + time.

▋ for date extraction: ❶ uses the Left function to extract the previous date numbers.

= LEFT (A2pai8)

❷ converts the date number string format to a date format text string, here we use the Text function.

= TEXT (number, format code)

= TEXT (LEFT (A2Magol 8), "0000-00-00")

❸ changes the text-type date string into a number, we just need to add-- to the formula.

Then modify the number format to a short date, which can be displayed as a date. After the number format is converted to the date format:

▋ for time extraction: as shown in the following figure, we use the Right function to extract time.

= RIGHT (A2pai8)

We know that the essence of date is numerical value, and the essence of time is a decimal, so date time is date + time.

At this time, we have changed the non-standard date and time into the standard date-time format through the formula.

Merge the formula again.

=-- TEXT (LEFT (A2jue 8), "0000-00-00") + RIGHT (A2Jing 8)

Back to the case, the formula is used to convert the submission time and operation time into the standard format.

The date and time of the two are subtracted, because what we need is the length of time, that is, the number of hours, and because 1 day equals 24 hours, we still need * 24.

Small supplement: use the array formula to merge three formulas, if you don't understand it here, we can also write a long point directly, subtract the two dates and times and then multiply them.

PS. In addition to Office365, the array formula needs to be finished by pressing the triple key [Ctrl+Shift+Enter]. It is not possible to press enter after entering the formula.

= SUM ((--TEXT (LEFT (A2LEFT (A2LEFT), "0000-00") + RIGHT (A2Vera B2pinger 8)) * {- 1Pol 1}) * 24

At this point, the method of Excel function is introduced.

Next, let's move on to the explanation of the PQ method.

02. PQ when it comes to PQ, the first step, you know what we're going to do, right?

No matter three, seven or twenty-one, import our data source into PQ.

Select the data source-[data] tab-[from Table / region]-[OK].

PS. Different versions of Excel import operation paths may be slightly different.

In PQ, although the format such as 20220823 18:00:00 can not be directly recognized and converted into a date-time format by the DateTime.From function, it can recognize the date on the left and the time on the right.

So we can do the conversion by replacing the null value with T.

Let's look at the interface operation.

❶ replacement value. Select two columns-under the conversion tab-click replace value-the value to find is: null-replace with: t.

❷ identifies the date. Select the two columns, under the conversion tab, click the analyze button below the date, and then simply modify the formula.

Since the analysis function on the interface is only date / time, there is no date-time type of analysis, so we also need to change the formula.

Before changing:

1. = Table.TransformColumns replacement value, 2. "submission time", each Date.From (DateTimeZone.From (_)), type date}, 3, {"Operation time", each Date.From (DateTimeZone.From (_)), type dat) after change:

= value replaced by Table.TransformColumns, "submit time", each DateTime.From (_)}, {"Operation time", each DateTime.From (_) ▲ swipe left and right to view

Operation effect of moving picture:

❸ adds one column to calculate the difference between the two columns. Under the [add column] tab-[Custom column]-enter a formula-[OK].

= Number.From ([Operation time]-[submission time]) * 24

At this point, the effect has been completed, and the data will be exported for further analysis.

If you're still wondering how to export PQ data, you shouldn't.

This is the end of the introduction to the interface practice of PQ.

What I'm going to talk about next is another method of PQ (M function), which is a little more difficult, and the purpose is to expand your thinking. If you're not interested, you can skip this Part~.

03. Extension idea 1: replace the string with a text-type date and time that can be recognized by PQ, and return it by using the DateTime.From function.

The formula is as follows:

1. = Table.AddColumn (2, Table.TransformColumns (source, {}, each DateTime.From (Text.Replace (_, "T")), 3, "time difference", 4, each Number.From ([operation time]-[submission time]) * 24)

Idea 2: identify the corresponding format information one by one through DateTime.FromText

Little Tips:

Format: "yyyyMMdd HH:mm:ss"

The abbreviations for month and minute are both m, so to distinguish, uppercase M represents month, lowercase m represents minute, H (24 hours), h (12 hours).

The time here is in the form of 24 hours, so this is H.

The formula is as follows:

1. = Table.AddColumn (2, Table.TransformColumns (3, source, {}, 4, each DateTime.FromText (_, [Format= "yyyyMMdd HH:mm:ss"])), 5, "time difference", 6, each Number.From ([operation time]-[submission time]) * 24)

Only 1% of the electricity is left.

04. Finally, this article introduces "how to calculate the time between two dates and times?" "

If the date and time is standardized, it is very simple and directly subtracted.

But in the actual work, the vast majority of what we are faced with is non-standard date and time, so we need to master the method of transformation.

What is often used in the Excel function is the Text function, which formats the value text into a standard date format, followed by numerical conversion.

In PQ, the analysis function can identify most irregular dates, but a few still need to be skillfully converted.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, editors: Yali Zi, 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.