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

The original title: "1 minute!" Learn to record time automatically with Excel and bid farewell to manual inefficiency! "

Hello, everyone. I'm Xiaolan who knows a little bit about Excel.

In Excel, there are many "automatic" functions that can greatly improve the office efficiency of cousins, such as:

❶ automatically highlights the entire row of data.

❷ automatically merges worksheets.

Automatic reminder of ❸ expiration.

……

What I'm going to tell you today is also an "automatic" function:

After entering data in Excel, the time is automatically recorded. After you modify the data, you can either change the date and time with the change, or you can keep it unchanged.

I don't understand? Let's take a look at the moving picture:

▲ modify data, date and time change

When ▲ modifies data, the date and time is constant, compared with manual input time, automatically recording modification time is more efficient and less likely to make errors.

Still don't understand? Never mind. Let's look at specific cases.

Modify the data, date and time change Akiba store needs to record the declaration and the time of products in and out of the warehouse, and if there is a situation in and out of the warehouse, you have to modify the inventory. As shown below:

(for a better demonstration, only part of the data is shown here. )

However, due to the large number of products, each record has to be checked many times, always worried that the time is not remembered, wrong, the inventory quantity has not changed and so on, especially troublesome.

What should I do?

At this point, we need to use the technique of "recording time automatically", and we also need to let the date and time change as the data changes.

In this way, we can clearly know whether the inventory, in / out of the warehouse has been registered.

When you open it later, you can also see the time of the last modification, which is convenient for stocktaking.

How do I do that? We can use VBA code.

VBA?!

Calm down. Don't panic.

You don't have to write it yourself, there are already a lot of ready-made code written by big names on the network, we just need to apply it.

👉, take a look at the steps:

❶, open VBA.

Press [Alt] + [F11] to open the VBE window, the programming tool for VBA, where we write VBA

❷ copies the following code into the code window of [Sheet2] (the worksheet where the data is located).

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Count > 1 Then Exit SubIf Target.Row = 1 Then Exit SubIf Target.Column Mod 2 > 0 Then Exit SubIf Target = "" Then Target.Offset (, 1) = "" Else Target.Offset (, 1) = NowEnd Sub

PS: be careful that the code doesn't misplace the worksheet.

❸, just close the window.

After that, enter the data in the even column, and the next column will automatically display the time.

Finally, save the table file as an Excel macro-enabled workbook (* .xlsm) (note: be sure to save the Excel macro-enabled workbook (* .xlsm))

All right, that's all for this little trick.

Some students will ask, what should I do if I don't need the date and time to change with the modified data?

Don't worry, let's look at the second case.

Modify the data, keep the date and time unchanged, Xiao Zhu needs to purchase every week, and record the purchase time, as shown below:

As there are too many types of each purchase and time is tight, the product name and time are very easy to get wrong.

At this point, you can use the technique of "recording time automatically" mentioned above.

And, if the product name is accidentally typed wrong, the date and time can remain the same after modifying the data.

How to operate 👉:

❶ check cell B2 and enter the formula:

= IF (A2 = ",", IF (B2 = ", NOW (), B2))

PS:A2 is the cell for entering the product name, and B2 is the cell for entering time, which can be modified according to the actual needs.

Meaning of the formula:

= IF (A2 = ",", IF (B2 = ", NOW (), B2)) if A2 cell is empty, B2 cell is also empty; if A2 cell has content, the value of the second IF function is displayed.

The second IF function means that if the B2 cell is empty, the value of the NOW function is displayed; otherwise, the original value is returned. (avoid changing the time of the computer, the contents of the B2 cell have also changed. )

The ❷ drop-down populates to other cells.

There is a circular reference to the ❸ formula, so you need to allow Excel to iterate.

Open [File]-[options]-[Formula], check "enable iterative calculation", and make sure.

❹ sets the cell format of column B to date and time.

Select column B, open [Ctrl+1] to format cells, and select "date"-"13:30 on 2012-3-14".

After that, enter data in column A, and column B will automatically record the time.

To sum up, this paper mainly talks about two methods of "inputting data and recording time automatically".

According to the requirements of different scenarios, you can choose different methods:

❶ VBA method: after the data is modified, the date and time changes.

❷ function method: after modifying the data, the date and time remain unchanged.

When you encounter a scene that needs to record time with Excel, be sure to read this article.

Tips:

❶ in order not to affect other formulas, before closing the table with "iterative calculation" enabled, uncheck it and enable it when it is opened again.

In order to avoid loss, ❷ suggests that after the input is completed, the generated time record should be changed from formula to pure value by the method of "selective paste-value".

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