Create a list of business days with Sunday to Thursday workdays

0. Background

This module demonstrates ways to create a list of business days for regions that have a Sunday to Thursday workweek, equivalent to a Friday and Saturday weekend. A list of countries with a Sunday to Thursday workweek in provided in section 3.

1. Example - Johor state, Malaysia

A case is demonstrated for Johor in Malaysia, chosen because of its nearness to Australia in the Asia-Pacific region. Johor state moved to the new weekend structure in 2014, joining the other states of Kedah, Kelantan, and Terengganu.

The following examples show two techniques to create lists of business for calendar year 2019 with adjustment for regional holidays.

1.1 WORKDAY.INTL function

The method is based on the WS WORKDAY.INTL function.

WS functionDescription
WORKDAY.INTL(start_date, days, [weekend], [holidays]) Returns the serial number of the date before or after a specified number of workdays with regional weekend parameters
1 - Saturday, Sunday
2 - Sunday, Monday
3 - Monday, Tuesday
4 - Tuesday, Wednesday
5 - Wednesday, Thursday
6 - Thursday, Friday
7 - Friday, Saturday
11 - Sunday only
12 - Monday only
13 - Tuesday only
14 - Wednesday only
15 - Thursday only
16 - Friday only
17 - Saturday only

To create the list:

  1. Add a list of regional holidays to the workbook. These are assigned to the range name Holidays.Johor in column G (figure 1)
  2. Enter a seed value for the first date in the series, cell C3 of the example (value 1-Jan-19). Ensure that it is a valid business day (check a calendar!)
  3. Enter the following formula in cell C4: = WORKDAY.INTL(C3,7,Holidays.Johor). This is the cell below the seed date
  4. Using the fill handle, drag this formula down until the value reaches 31-Dec-19, at row 248
Fig 1: Business day list - column C. Created using the WORKDAY.INTL function with weekend parameter set to 7 (Friday, Saturday)
  1. Optional: convert all the formulas to values with Copy - Paste Special, Values
  2. Optional: identify the end of week days (Thursday) with a conditional format
    • Select the date range: C3:C248
    • Select the ribbon sequence: Home > Styles > Conditional Formatting > New Rule ... (figure 2)
    • xlf-workday-conditional-format
      Fig 2: Conditional format - column C. Created using the WORKDAY.INTL function with weekend parameter set to 7 (Friday, Saturday)
    • The logic for a conditional format formula is similar to that for an Advanced Filter, Complex Criteria. The formula uses a relative reference to the first cell in the selection (cell C3) at the time the rule was created
WS functionDescription
WEEKDAY(serial_number, [return_type]) Returns the day of the week identified by a number from 1 to 7
1 - Numbers 1 (Sunday) through 7 (Saturday)
2 - Numbers 1 (Monday) through 7 (Sunday)
3 - Numbers 0 (Monday through 6 (Sunday)
11 - Numbers 1 (Monday) through 7 (Sunday)
12 - Numbers 1 (Tuesday) through 7 (Monday)
13 - Numbers 1 (Wednesday) through 7 (Tuesday)
14 - Numbers 1 (Thursday) through 7 (Wednesday)
15 - Numbers 1 (Friday) through 7 (Thursday)
16 - Numbers 1 (Saturday) through 7 (Friday)
17 - Numbers 1 (Sunday) through 7 (Saturday)

1.2 Advanced Filter method

The advanced filter is a three stage process:

Refer to figure 4.

  1. Create a list of calendar days for 2019, using the ribbon sequence Home > Editing > Fill > Series .... See figure 3 for the Series parameters. The calendar days are in the range C5:C369 (365 rows). This range, plus the label in C4 will form the Advanced Filter, List Range
  2. xlf-week-series-parameters
    Fig 3: Series dialog box - Series in: Columns Type: Date, Date unit: Day, Step value: 1, Stop value: 31Dec19
  1. Develop a filter criteria to hide the Friday, Saturday weekend dates, and listed holidays (F4:H5). Each formula includes a logical operator, and individually returns TRUE or FALSE. Because the three formula of the filter criteria are in the same row, the criteria in only TRUE if ALL formulas return TRUE. The criteria is equivalent to =AND(COUNTIF(Holidays.Johor,C5)=0,NOT(WEEKDAY(C5,1)=6),NOT(WEEKDAY(C5,1)=7)) in a single cell
  2. Call the Advanced Filter with ribbon sequence Data > Sort & Filter > Advanced
  3. xlf-workday-advanced-filter
    Fig 4: Advanced filter dialog box - Action: Copy to another location, List range: $C$4:$C$369 (include a label: Dates ), Criteria range: $F$4:$H$55 (a 2 x 3 array), Copy to: $K$4 (upper left cell of target)
  4. xlf-workday-filter-criteria
    Fig 5: Advanced filter - Criteria range: {"Filter criteria",,;TRUE,TRUE,TRUE} (a 2 x 3 array). Includes a single cell label in element (1,1), and three logical formula on the same row (2), equivalent to an AND structure
  5. The output (Copy to:) is shown in column K of the Fill > Series worksheet (figure 6). The Dates label is the same as the source

Fig 6: Workday advanced filter - List range (left arrow), Criteria range (centre arrow), and Output - Copy to range (right arrow)

3. Countries with a Sunday to Thursday working week