Practical Excel skills sharing: 13 date input methods, how many of them do you know!

Entering a single date is easy, but what about hundreds or thousands of date data? If you only know the most basic manual income, or know how to use ctrl;, it will be really difficult to complete the entry work quickly. In fact, there are 13 methods for date entry, and different methods are suitable for different entry needs. Some are suitable for entering today's date, some are suitable for inputting multiple non-consecutive dates, and some are suitable for automatically recording dates. Only by understanding these methods can you find the fastest and most applicable date entry method according to your needs.

We can’t avoid entering various dates in excel in our daily work. Let’s share with you a summary of date input methods that we have recently sorted out. See See if there is a method that better suits your needs.

The first type: the universal method that everyone knows, completely manually input the date - Type 1

Manually input the date, the usual format is "2018-10 -27" or "2018/10/27", then hit the Enter key. This method is suitable for inputting all dates. The only disadvantage is that it is slow!

The second category: Several ways to quickly enter the date of the day

To enter the date of the day, you can abandon manual input and use some shortcuts method. According to the input effect, it can be divided into two categories: those that are fixed after input and those that change with the system after input.

1. The date is fixed after input

1) Shortcut key ctrl;——The second type

is selected In the cell where you need to enter the date, press the ctrl; key. Note; (semicolon) key must be half-width English. Enter the current date in this input method, and this date will not change if you open it in the future.

If you are inputting the current time: shortcut key ctrl shift;

If you are inputting the current date and time: first press the shortcut key ctrl;, then press the space once, and then press the shortcut key ctrl shift ;

2) Directly input the letters rq using input methods such as Sogou/Google - Type 3

Use input methods such as Sogou/Google to input the letters rq, that is It can automatically pop up the date style selection, as shown below:

The best thing about this input method is that it can input the lunar calendar!

If you enter the current time: enter the letters sj

If you enter the current day of the week: enter the letters xq

2. After the date is entered, it will change according to the system

1) Today function inputs the current date - Type 4

Enter the formula function =today() in the cell where you want to enter the date, you can enter Current system date. The biggest feature: The date will change as the system date changes. What I entered today is 2018/11/4, and when I open it tomorrow, it will become 2018/11/5.

2) The text function inputs the date with the day of the week - Type 5

Enter the formula function =TEXT in the cell (TODAY(), "MM, DD, YYYY, DD, AAAA"), and then press the Enter key to obtain the system time with the day of the week. Look at the picture below.

3) Now function inputs the current date with time - Type 6

Enter the formula function in the blank cell "=now()" gets the current date with time. As shown in the figure:

The third category: quickly record a large number of dates

If there is a lot of date data that needs to be entered, the previous Shortcut methods won't work. The following method can be used depending on whether the input dates are consecutive.

1. Continuous input of dates

1) Filling method - Type 7

For example, enter 2018/1/ 1 to 2018/1/31.

First enter the date 2018/1/1 in cell A1, then click the "Home" tab - "Edit" option group - "Fill"/"Sequence" to open the "Sequence" dialog box , as shown in the figure:

In this dialog box, we set "Sequence generated in" as column, "Type" as date, date unit as day, and step value as 1. The end value is 2018/1/31. Finally, click "OK" to realize automatic filling of consecutive dates. Please see the operation demonstration animation↓↓↓

In addition, for date data, you can further select the "Date Unit" area on the right through this filling method to achieve continuous input by working day, month and year. You can try it out!

2) Drag method - Type 8

For example, enter 2018/1/1 to 2018/1/31.

First enter "2018/1/1" in cell A1, then drag the "fill handle" all the way to A31, let go, and you will see that the cell is automatically filled with 2018/1/1 to 2018/1/31. Please see the operation demonstration animation↓↓↓

Warm reminder: What should I do if Excel does not have the auto-fill function?

How to open: Click the "Options" button in the "File" tab, click the "Advanced" tab in the pop-up "Excel Options" dialog box, and check " Enable fill handles and cell drag-and-drop functionality" and click OK. The autofill function appears!

2. Date discontinuous input

is divided into two types: current discontinuous date input and discontinuous date input in different years. Condition.

1) Short date input non-consecutive dates of the current year - Type 9

For example, enter: 2018/7/30, 2018/3/5, 2018/ 6/12...

The operation steps are as follows:

(1) Set the format to short date format in advance. Select the date entry area, and then click the "Home" tab/"Number" option group/"General" drop-down button/"Short Date" format.

(2) Only enter the month and day (such as 7-1 or 7/1), and then change the format to a long date after inputting it

2) Different years Continuous date input

For example, input: 2012/7/2, 2016/7/4, 2017/10/11...

Two methods to solve: simple number separation, Simple digital text function

(1) Simple digital column input - Type 10

Manually enter "/" or "-" to separate the year, month and day data, if the amount of data is large , input will be very slow. Is there any way to deal with it? Let me tell you, you can use the data disaggregation method, you can’t think of it! Then let us witness it. Just simply enter the numbers 20120702, 20160704, etc., and then sort them. The following takes the recorded date data as an example.

Operation steps:

Select column B, click the "Data" tab/"Column" button in sequence to enter the text column wizard interface. Select "Delimiter" under the most appropriate text type column, then click Next, only check "Other" and enter "/" in the box on the right, and then click "Next". Select "Date: YMD" in the column data format column, and finally click "Finish" to easily separate the year, month and day data. The dynamic demonstration process is shown in the figure below:

(2) Simple digital text function - the 11th

is the same as the previous method, only enter numbers such as 20180412, and then enter function =TEXT(A2,"0-00-00"). See the picture below:

The date format obtained is different depending on the content in the quotation marks in the Text function. For details, please refer to the last table of this article for input.

Category 4: Real-time automatic time input

Customer return visit forms, electronic sign-in forms, etc. generally require real-time input of time based on data records, for example, 3:30 p.m. For those who register for a return visit, the return visit time will be automatically entered as 2018/11/4 15:30 during registration. How to do it?

1) VBA method - type 12

The after-sales service department of a company has an after-sales service return visit registration form. After each return visit to a customer, the following form is required: Fill in the relevant information in the registration form and record the return visit time. Can you slightly reduce the workload of entering information?

What I want to achieve for the time being is that the return visit time can automatically obtain the current time of the computer system after recording the previous information, and it will not change with the change of the system time. At this time, what my mind reacts to is to challenge VBA, haha.

The operation steps are as follows:

(1) Under the current worksheet, press the f11> key combination to open the VBA editing window, and then click on the corresponding Sheet on the left Double-click and paste the following code in the blank space on the right:

Private Sub Worksheet_Change(ByVal Target As Range) 'Trigger the event after editing the cell

If Target.Column = 3 Then 'If the selected cell column value is equal to 3

Target.Offset(0, 1).Value = Format(Now, "yyyy-mm-dd hh:mm") 'Indicates that based on the target cell, add one column to the column number and output the current system time as a new cell value without any change

End If

End Sub

(2) Then close the window, you can enter the customer information immediately after the return visit to get the current return visit time, and the time will not change. If you don’t believe it, watch the animation below.

2) Function method: IF NOW function - Type 13

In addition to VBA, are there any other methods? What about automatic recording of time? After thinking about it for a long time, I finally thought of it, what is the method? Please continue to read patiently.

Use the following table to register employee check-in times as an example.

The steps are as follows:

(1) Set the iterative calculation of the formula

Open "Options" under the "File" tab , check "Enable iterative calculation" in the "Formula" tab, leave the others as default, and then click "OK". Why enable iteration? Because our subsequent formula uses circular references, it can only run properly if iteration is enabled.

(2) Enter the formula. Enter the formula in cell D3 - =IF(C3="","",IF(D3="",NOW(),D3)), and copy the formula downwards.

(3) Modify the time format. Because column D is in a regular format, this step requires modifying column D to a precise time format. Select the data area in column D, set the data type to date in the cell format, and the type is "2001/3/14 13:30".

At this time, when we enter "Complete" in column C, the current entry time will automatically appear in column D.

The above VBA and IF NOW formula function methods are very helpful in solving some forms that require accurate and timely recording of time. Hurry up and practice.

What I shared above is the input of dates. Below I will share three format adjustment methods after inputting dates.

Category 5: Quickly change date formats

1) Quickly switch between long and short date formats

Short date 2018/1 /10 and the long date format of January 10, 2018, the quickest way is to select short date or long date in the "Home" tab/"Number" option group to switch.

2) Ctrl 1 custom format

With Ctrl 1 custom format, you can quickly change the date to what you want format. Remember: in date format, Y represents the year, M represents the month, and D represents the day. Here are two examples.

changed to: 2018.10.8 format

changed to: 2018-10-8 format

3) Text function method to change the format

In addition, the Text function can also be used to quickly convert multiple date formats. The formula usage is: Text (cell, converted format ), see the table below for specific examples.

I wonder if you have more information about the input methods or scenarios recently. Welcome to leave a message to exchange!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: 13 date input methods, how many of them do you know!. For more information, please follow other related articles on the PHP Chinese website!