How to auto fill date in excel, with increment time by 5 minutes and generate numbers in batch

Dates and times can be automatically filled, except for numbers in Excel; dates can be filled by day, month, year, and weekday, and time can be filled hours, minutes, and seconds in increments. There are three kinds of methods of filling date, one is to automatically fill by dragging, the second is to automatically fill in the method of double-clicking the cell fill handle, and the third is to fill in large numbers with series, such as filling for one year, several years or even for decades.

In addition to the date and time can be automatically filled in as the date or time. You can also use them to generate the numbers in batches. You can use only the date and time to generate the numbers and the dates plus the custom numbers to generate the numbers. The former can be realized by a custom format and a simple formula. The latter needs to be implemented with the Row and Text function.

I, How to auto fill date in excel

(I) Automatically fill by day, month and year respectively

1. Auto fill with dragging down. Select the cell A1, move the mouse to the cell fill handle in the lower right corner of A1, hold down the left button, drag down until the last row to be filled, and the cells that pass through are automatically filled with dates that are gradually increasing in number of days. Click the "Auto Fill Options" icon and select "Fill Weekdays" in the pop-up menu, then Saturday and Sunday are not included in the filled date. Click the "Auto Fill Options" icon again, select "Fill Months" in the pop-up menu, the month will be automatically incremented, and the day will not be incremented. Click the "Auto Fill Options" icon, and select "Fill Years" in the pop-up menu, the year will increase, the day and month will not increment. Operation process steps, as shown in Figure 1:

2. Auto fill shortcut. Select the cell B1, move the mouse to the fill handle of B1, double-click it, the rows in which there are data are auto filled with dates that are gradually increasing in number of days. Operation process steps, see screenshot in Figure 1:

Figure 1

(II) Filling with "series"

1. Select the cell A1, press and hold Alt, press H once, press F once, press I once, press S once, open the "Series" dailog, select "Column" for "Series in", "Step value" is reserved 1,  input 2020/10/27 for "Stop value", press Enter, then generates the date of one year by "day". Then open the "Series" dailog again with the shortcut above, "Series in" still select "Column", input 7 for "Step value", also input 2020/10/27 for "Stop value", press Enter, then generates a date every 7 days, always generates to 2020/10/25; operation process steps, as shown in Figure 2:

Figure 2

2. Tip: Open the "Series" dialog, you can select the "Home" tab, click the "Fill" icon in the "Editing" group  in the upper right corner of the screen (there is a blue arrow pointing down), select "Series" in the pop-up menu.

3. Here you can also generate the date by year, month and day and Weekdays, just select the corresponding option in the "Series" dialog.

(III) Excel automatically fills the current date and time(Auto fill date and time in excel)

1. Select the cell A1 and press the shortcut key "Ctrl + ;" to fill the current date to A1; select the cell B1 and press the shortcut key "Ctrl + Shift + ;" to fill the current time to B1; see screenshot in Figure 3:

Figure 3

2. The current date and current time filled with the function formula are automatically updated, and they are not automatically updated with this method.

II, Excel auto fill time, each increment of 5 seconds or 5 minutes

(I) Excel increment time by 5 seconds

1. Select the cell A2, copy the formula =A1+"0:0:05" to A2, press Enter, return to the time after incrementing by 5 seconds; move the mouse to the cell fill handle in the lower right corner of A2, press and hold left button of mouse, drag down, then the cells that are passed are automatically filled in increments of 5 seconds; the process steps, as shown in Figure 4:

Figure 4

2. It can be seen from the operation process that after the second is incremented to 60, the minute will automatically increase by 1,10:55:56 to 10:56:01.

(II) Excel increment time by 5 minutes, with date and time in cells

1. Select the cell A2, copy the formula =A1+"0:05" to A2, press Enter, and return to the incrementing time of 5 minutes; also drag down to automatically fill the time of other cells, the operation steps , as shown in Figure 5:

Figure 5

2. The time in the example is about to transition to a new day time 2019/10/27 23:51:55, when the time increases to 56 points, continue to increase 5 minutes, transition to 2019/10/28 0:01, that is, the date and hour are automatically incremented.

III, Excel uses the date and time, date and serial number to generate the batch number.

(I) Generate numbers by dates and times

1. Custom format. Select the cell A1, press Ctrl + 1, open the "Format Cells" dialog, select the "Number" tab, then select "Custom" on the left, copy yyyymmddhhmmss to the right under "Type", press Enter, then the date and time is removed from the colon, and the operation steps are as shown in Figure 6:

Figure 6

2. Generate a number. Select the cell A2, copy the formula =A1+"00:00:01" to A2, press Enter, then generate a number, generate other numbers with the drag down method, the operation steps, as shown in Figure 7:

Figure 7

(II) Generate numbers by dates and serial numbers in batch

1. If you want to generate a number with the current date + 000001. Select the cell A1, copy the formula =TEXT(TODAY(),"yyyymmdd")&TEXT(ROW(),"000000") to A1, press Enter, return to 20191028000001, and generate another numbers with the drag-down method. Operation process steps, as shown in Figure 8:

Figure 8

2. Formula description: =TEXT(TODAY(),"yyyymmdd")&TEXT(ROW(),"000000")

A. In the formula, TODAY() is used to return the date of the day, and TEXT(TODAY(),"yyyymmdd") is used to return the date by four "years", two "months" and two "days".

B. In the formula, ROW() is used to return the current row number, and TEXT(ROW(),"000000") is used to output the returned row number in 6 bits, that is, the row number of less than 6 digits, and the front is complemented by 0.