How many weekends in a month

By SpreadCheaters
Page last updated: 18/10/2022 | Next review date: 18/10/2024
close

Author bio


SpreadCheaters
View More north_east

Understanding the syntax

  • Suppose we have an excel worksheet with month and year inputs in seperate cells as shown.
  • To get the list of weekends in the given month of the given year we are going to use the excel function WORKDAY.INTL.
  • This function has the following syntax.

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

  • The first argument in this function is the start date.
  • The start date in our case is going to be another formula from excel called the DATE formula with the following syntax.

=DATE(year, month, day)

  • The year and month arguments are going to be the cell inputs already provided in the excel sheet.
  • However, for the day input we need the last day of the previous month because in case the first day of the desired month is a weekend we want to have it listed.
  • For the days input in the WORKDAY.INTL we want to have a single day increment so that we don’t miss out any day that is a weekend. This argument is therefore going to be the formula,

=Row()-row number of cell above the formula cell

  • Here the Row() formula outputs the row number of the active cell and the row number above the formula is subtracted to create an increment of 1.
  • Next argument is optional and allows us to mention weekends as a sequence of ones and zeros. Workdays are set as zeros and weekends by ones between inverted commas such as “0000011”. This code indicates that weekdays are Monday through Friday represented by zeros, whereas weekends are Saturday and Sunday represented by ones. Since this sequence returns the workdays as output, we need to invert this code for getting weekends as output. In our case the code will be “1111100”.
  • We are going to leave the fourth optional argument in WORKDAY.INTL as is because it is only required when the output needs to account for holidays which in our case does not.
  • We also have to restrict our function to the selected month and so we will add an IF function block outside the WORKDAY.INTL as below,

=IF(MONTH(WORKDAY.INTL(start_date, days, [weekend], [holidays]))=given_month, WORKDAY.INTL(start_date, days, [weekend], [holidays]), “”)

  • The above function checks if the month of the WORKDAY.INTL function is the same as the given month and shows the output, otherwise keeps it blank.

Excel has an extensive list of functions to work with dates. In order to get a list of weekends in a month we can use a combination of the date functions in excel and apply tweeks to get the desired results. So let us find out.

Step 1 – Create & Implement the Formula

– So now that we understand our formula and the arguments write the formula as follows,

=IF ( MONTH ( WORKDAY.INTL ( DATE ( A2, B2, 0 ), ROW() – 5, “1111100” ) ) = B2, WORKDAY.INTL ( DATE ( A2, B2, 0 ), “”)

– We also need to lock the month and year cells in the formula i.e.
A2 and B2 by pressing F4 key to change them to $A$2 and $B$2.

– Press enter to implement the formula.

– The result will show the first weekend of the month.

– Drag the fill handle down to return the entire weekend list for the given month as shown below.