The dataset showcases Names of Candidates for an interview.
To AutoFill their interview dates:
Method 1 – Dragging the AutoFill Handle Icon
Use the Fill Handle.
Steps:
- Select C5.
- Enter a date in an Excel date format. Here, MM/DD/YYYY:Â 12/07/2022.
- Drag the AutoFill Handle to fill the rest of the dates.
- You will see a small square box at the right bottom corner. Click the drop-down menu.
- To fill the cells with increasing dates, select Fill Series or Fill Days. To fill the cells with increasing weekdays, select Fill Weekdays. To fill the cells with increasing months, select Fill Months. To fill the cells with increasing years, select Fill Years.
- Here, Fill Weekdays was selected.
- This is the output.
Note: The step increment in this method is 1. To use any other increment, fill the first two cells with that increment.
For example, to fill the cells with dates increasing by 3 in each step, enter the first two dates: 12/07/2022 and 12/10/2023. Select the two cells together and drag the AutoFill Handle.
This is the output.
Read More:Â How to Create Automatic Rolling Months in Excel
Method 2 – Applying the Fill Tool in the Excel Toolbar
Steps:
- Select C5.
- Enter a date.
- Select C5:C14.
- In the Home tab, select Fill.
- Choose Series in Editing.
- In the Series dialog box, in Series in, select Columns.
- In Type, select Date.
- In Step, enter the increment. Here, 3.
- In Date, select Day, Weekday, Month, or Year (here, Weekday).
- Click OK.
- The cells are filled with increasing weekdays by 3 without dragging the Autofill.
Read More: How to AutoFill Months in Excel
Method 3 – Using a Formula to Autofill Dates
3.1 Autofill Dates with Repetition
To fill the cells with random dates between December 7, 2022, and December 27, 2022, with repetition, use the RANDBETWEEN and DATE functions:
Steps:
- Select C5.
- Enter the formula:
=RANDBETWEEN(DATE(2020,12,7),DATE(2020,12,27))
- Press Enter.
- Double-click or drag the Fill Handle to fill the rest of the cells.
The cells are filled with random dates between December 7, 2022, and December 27, 2022.
- This is the output.
Don’t use a customized date within a formula. Use the DATE function.
Read More:Â How to Copy Same Date in Excel
3.2 Autofill Dates without Repetition
To autofill the cells with random dates between December 7, 2022, and December 27, 2022, without repetitions, use the RANDBETEWEEN, DATE, INDEX, FILTER, COUNTIF, SEQUENCE, and ROWS functions.
Steps:
- Select C5.
- Enter the formula:
=RANDBETWEEN(DATE(2020,12,7),DATE(2020,12,27))
- Press Enter.
- Enter this formula in C6:
=INDEX(FILTER(DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,07),1,7,1)),COUNTIF($C$5:C5,DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)))=0),RANDBETWEEN(1,ROWS(FILTER(DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)),COUNTIF($C$5:C5,DATE(2022,12,SEQUENCE(DATE(2022,12,27)-DATE(2022,12,7),1,7,1)))=0))),1)
Within the SEQUENCE functions, DATE(2022,12,27)-DATE(2022,12,7) indicates the last day – the first day of the duration.
7 is the day number of the starting date (December 7).
Within the DATE functions, 2022 and 12 are year and month of the starting date (December 7, 2022).
Within the COUNTIF functions, $C$5:C5 is the first cell in the range.
- Press Enter.
- Double-click the Fill Handle icon or drag it down.
- This is the output.
Read More: How to Increment Month by 1 in Excel
Download Practice Workbook
Download the practice workbook.
AutoFill Dates: Knowledge Hub
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Days of Week Based on Date in Excelhttps://www.exceldemy.com/learn-excel/unique-values/count/
<< Go Back to Excel AutoFill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
I want to enter a date in excel for a work schedule. Then the days of the week will auto populate based on the date entered.
WORK WEEK ENDING [J:1] 3/17/2022
F:6,H,J,L,N,P,R would auto populate with R:6 matching J:1.
Thanks for any ideas.
Hi SteelWolf,
I am not sure what you meant exactly by that. But assuming you want to put in a date in cells F6, H6, J6, L6, N6, P6, and R6 that match with cell J1, you can use formulas in those cells with reference to cell J1. For example, let’s assume you want the previous date, exact date, and the following date of cell J1 in cells F6, H6, and J6.
You can write =J6 in cell H6. Write =EDATE(J6,-1) in cell F6 and =EDATE(J6,1) in J6.
You can modify/ use different formula according to your needs in this way.
But if you want you use the “autofill” in those scattering cells, it is not possible yet as of the latest version of Excel.
Hope that helps. If you meant something else entirely, please let us know.