Office hacks: quickly list all the dates list

If we are creating a data table in which we need to use a list of data by date, in this case, if you enter the list of dates one by one, the efficiency will be very low. If you can use the automatic filling and function functions provided by Excel to automatically fill the date sequence, the efficiency will be greatly enhanced, so that the date is no longer afraid of more. The following two methods can achieve automatic generation of date sequences.

1. use the fill handle to create a list of consecutive dates

In filling data, Excel has a fill handle, which is also suitable for filling dates. Although it can not directly create a list of dates between two given dates, but with the help of this tool to create a continuous date is also very easy.

First, enter the starting date in the first cell of the date column (e.g., A2), then click anywhere outside the cell and click the cell again to activate the fill handle (i.e., the small square in the lower right corner becomes visible) (Figure 1).


Click and pull down the fill handle (or double-click the fill handle box), you can create a list of consecutive dates in Excel. If the created list is less than the cut-off date, continue to use the same method to drop down and create subsequent sections; if the cut-off date has been exceeded, simply delete the excess section (Figure 2).


2. Use a formula to create a list of all dates

If you must get a list of dates between two dates exactly and automatically, then using a formula is a safer solution.

First, you need to calculate a time difference, that is, the number of days between the two dates, to calculate the time difference in order to find out how many spaces to leave. Suppose we enter the start date into cell B2, the end date into cell C2, and then enter the formula “= DATEDIF (B3, C3, “d”)” in cell D2, so that you can automatically calculate the fill time difference in cell D2 (Figure 3).


Next, starting from the start date of the table, leave n-1 spaces for the reserved dates according to the time difference obtained (assuming n is the time difference obtained from the calculation), and fill in the end date after leaving the space. In this example, for the sake of simplicity and clarity, use the start date March 4, 2018 to the end date March 8, 2018 as an example.

Subsequently, select the middle region of the blank date cell, and then enter the following formula.

=IF(B$2+ROW(B1)<B$6,TEXT(B$2+ROW(B1), “e/mm/dd”),””)

formula input, press the keyboard Ctrl + Enter button, so that the middle of the blank area will be automatically filled with all the dates (Figure 4).


The actual calculation, just the above formula for the starting and ending cell variables, in accordance with the starting and ending cell location to do the corresponding changes, you can achieve the purpose of universal.


Leave a Comment