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)

Excel CEILING Function

This post will guide you how to use Excel CEILING function with syntax and examples in Microsoft excel.

Description

The Excel CEILING function returns a given number rounded up to the nearest multiple of a given number of significance. So you can use the CEILING function to round up a number to the nearest multiple of a given number.

The CEILING function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The CEILING function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the CEILING function is as below:

= CEILING (number, significance)

Where the CEILING function arguments are:

  • number – This is a required argument. The number that you want to round up.
  • significance – This is a required argument. The multiple of significance to which you want to round a number to.

 Note:

  • If either number and significance arguments are non-numeric, the CEILING function will return the #VALUE! Error.
  • If the number argument is negative, and the significance argument is also negative, the number is rounded down, and it will be away from 0.
  • If the number argument is negative and the significance is positive, the number is rounded up towards zero.

Excel CEILING Function Examples

The below examples will show you how to use Excel CEILING Function to round a number up to nearest multiple.

1# to round 4.6 up to nearest multiple of 3, enter the following formula in Cell B1.

=CEILING(4.6,3)

excel ceiling examples1

2# to round -4.6 up to nearest multiple of -3, enter the following formula in Cell B2.

=CEILING(-4.6,-3)

excel ceiling examples2

3# to round -4.6 up to nearest multiple of 2, enter the following formula in Cell B3.

=CEILING(-4.6,2)

 excel ceiling examples3

4# to round 2.6 up to nearest multiple of 0.2, enter the following formula in Cell B4.

=CEILING(2.6,0.2)

 excel ceiling examples4

5# to round 0.26 up to nearest multiple of 0.02, enter the following formula in Cell B5.

=CEILING(0.26,0.02)

 excel ceiling examples5


Related Functions

  • Excel ROUNDDOWN function
    The Excel ROUNDDOWN function round the number down to the specified number of digits. The syntax of the ROUNDDOWN function is as below:=ROUNDDOWN (number, num_digits)…
  • Excel ROUND function
    The Excel ROUND function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…
  • Excel ROUNDUP function
    The Excel ROUNDUP function rounds the number up to a specified number of decimal places. It will round away from 0.The syntax of the ROUNDUP function is as below:=ROUNDUP (number, num_digits)…