How many weekends are in a month using Excel

You can watch a video tutorial here.

In Excel, calendar dates are a type of data and are treated differently from text and numbers. Excel has many options for formatting dates and several functions for date calculations. Here we will see how to count the number of weekends in a month by adapting the NETWORKDAYS.INTL()date function. This function counts the number of working days between 2 dates. The syntax of the function is:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

• start_date: the date from which the weekends are to be counted
• end_date: the date to which the weekends are to be counted
• weekend: the days that are treated as weekends

Note: preset options are shown as weekends but you can define your own using a 7-digit notation where the first number represents Monday, zeroes (0) are workdays, and ones (1) are weekends e.g. “0000011” means that Saturday and Sunday are weekends and Monday to Friday are working days

• holidays (optional): non-weekend dates that are not to be counted as working days

Note: This function is available only from Excel 2010 onwards

Step 1 – Use the NETWORKDAYS.INTL () function

– In this example, we will adapt the NETWORKDAYS.INTL() function by defining only Saturday and Sunday as workdays since the function counts only workdays
– We will then divide the number by 2 to get the number of weekends for the month
– Type the formula using cell references:
= NETWORKDAYS.INTL(Start of the month, End of the month,”1111100”)/2

Step 2 – Copy the formula

– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Note: Decimal values are shown for those months where there is either only a Saturday or Sunday in the month. For example, 30^th April 2022 is a Saturday and 1^st May 2022 is a Sunday, so these months will show only half of a weekend.