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.
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!
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.
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 ;
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
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.
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.
Enter the formula function in the blank cell "=now()" gets the current date with time. As shown in the figure:
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.
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!
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!
is divided into two types: current discontinuous date input and discontinuous date input in different years. Condition.
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
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.
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?
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
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.
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.
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.
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
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!