How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then we can get a list of days.

But for some special requirement, we just want to fill weekdays or weekends into a list, for example if weekdays exist in the list, weekends are excluded. So, we need to find a way to only insert weekdays or weekends simply. In this article, we will show you the ways to fill days into list per your requirement properly, we provide two ways to fill weekdays or weekends, by Auto Fill function or the formula.

Method 1: Auto Fill Weekdays in Excel

Step 1: Launch excel open a new worksheet, enter the start date into any cell, if you want to fill only weekdays into the list, please enter a weekday, then drag it down to the next cells till we get the end date in date list. For example, we want to fill weekdays from 9/1/2020 to 9/15/2020, then enter 9/1/2020 in any cell, then drag it down to auto fill days till we get 9/15/2020.

Step 2: You may notice that when we dragging the cell down to apply formula to other cells, Auto Fill Options icon is displayed and attached to the last cell. Click on Auto Fill Options icon to load all options. Verify that ‘Fill Weekdays’ option is included.

Step 3: Check on ‘Fill Weekdays’ option, verify that days are updated from previous 9/1/2020 to 9/21/2020 automatically, that’s because weekends are removed from the list and weekdays are filled instead.

Step 4: If you want to keep the end date as 9/15/2020 in your list, just remove the extra days from the list. Now date list with only weekdays is created properly.

Method 2: Auto Fill Weekends by Formula in Excel

You can see that there is no fill weekends option in Auto Fill Options list. So, we need to find another way to fill weekends only. Actually, you can through below formula to fill weekends properly.

Step 1: Launch excel open a new worksheet, enter the formula =TEXT(“09/05/2020″+INT(ROW(A1)/2)+(CEILING(ROW(A1)/2,1)-1)*6,”mm/dd/yyyy”) into any cell. As you want to fill only weekends into the list, please enter a weekend date into the formula, in this sample we enter 09/05/2020 as start date, you can also change date format ‘mm/dd/yyyy’ to another format.

Step 2: Then drag it down to the next cells to apply the formula. Till we get the end date in date list. For example, we want to fill all weekends in September, drag it down till weekend date from October occurs.

Step 3: Just remove the date from October, then weekends from September are listed.

Related Functions

  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel INT function
    The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
  • Excel Ceiling function
    The Excel CEILING function returns a given number rounded up to the nearest multiple of a given number of significance.The syntax of the CEILING function is as below:= CEILING (number, significance)
Related Posts

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

Add Leading Zeros in Excel

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

How to Change Date to The Day of Week in Excel

In excel, we can type a date properly if set cell format as ‘Date’. If there is a list includes multiple dates, is there any way to convert these dates to the days of week accordingly? Actually, there are some ...

How to Create Increment Number with Texts in Excel

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...