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.
Dates for Malaysia Public Holidays 2020 are available at malaysia-public-holiday
1.1 WORKDAY.INTL function
The method is based on the WS WORKDAY.INTL function.
WS function | Description |
---|---|
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 [weekend] 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:
- Add a list of regional holidays to the workbook. These are assigned to the range name
Holidays.Johor
in column G (figure 1) - 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!) - Enter the following formula in cell
C4: = WORKDAY.INTL(C3,7,Holidays.Johor)
. This is the cell below the seed date - Using the fill handle, drag this formula down until the value reaches
31-Dec-19
, at row 248

- Optional: convert all the formulas to values with Copy - Paste Special, Values
- 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)
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
- Select the date range:
WS function | Description |
---|---|
WEEKDAY(serial_number, [return_type]) |
Returns the day of the week identified by a number from 1 to 7 [return_type] 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.
- 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
Fig 3: Series dialog box - Series in: Columns Type: Date, Date unit: Day, Step value: 1, Stop value: 31Dec19
- 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 - Call the Advanced Filter with ribbon sequence Data > Sort & Filter > Advanced
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) 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 - 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

3. Countries with a Sunday to Thursday working week
- Afghanistan
- Algeria
- Bahrain
- Bangladesh (Government offices and Banks)
- Djibouti
- Egypt
- Iran (parts)
- Iraq
- Israel
- Jordan
- Kuwait
- Libya
- Maldives
- Malaysia (Johor, Kedah, Kelantan, and Terengganu)
- Oman
- Qatar
- Saudi Arabia
- Sudan
- Syria
- United Arab Emirates
- Yemen
- Wikipedia Workweek and weekend
- Reference: Malaysia Public Holidays 2020
- Reference: Johor Public Holidays 2019
- Reference: Holidays in Johor in 2019
- Download the Excel file for this module: xlf-business-days-with-sun-thu-workdays.xlsx [22 KB]
- Development platform: Excel 2016 (64 bit) Office 365 ProPlus
- Published:: 24 August 2018
- Revised: Friday 24th of February 2023 - 10:38 PM, Pacific Time (PT)