WEEKDAY
Returns a number for the day of the week of a date.
WEEKDAY(<date>, <return_type>). Return type 1 (the default) counts Sunday as 1 through Saturday as 7. Type 2 counts Monday as 1 through Sunday as 7. Type 3 counts Monday as 0 through Sunday as 6.Returns: A whole number, as set by the return type.
What it does
WEEKDAY tells you which day of the week a date falls on, as a number. Finance teams use it to flag weekends, to find the business days in a month, and to move payment runs or bank transfers that land on a non-banking day. The return type decides how the numbers are counted. Type 2 is the usual choice for finance because it gives Monday as 1 and Sunday as 7.
Syntax and arguments
- date: any date or datetime value. Required.
- return_type: 1, 2 or 3, as described above. Optional, and 1 is the default. Use 2 for Monday-first numbering.
Example
A treasury team pays vendors on business days only. Calendar rows that fall on a weekend are flagged so the payment scheduler can skip them.
Is Weekend =
IF(WEEKDAY('Calendar'[Date], 2) >= 6, 1, 0)
With type 2, Saturday is 6 and Sunday is 7, so both return 1. Weekdays return 0. The flag is a simple number that can be summed or used as a filter.
Example: a second, different use
Business days in a month, counted from the same flag. The measure counts the Calendar rows where the flag is 0.
Business Days =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Is Weekend] = 0
)
Things to watch
- The default return type counts Sunday as 1. Many finance people expect Monday as 1, so state the type in every formula.
- WEEKDAY knows nothing about holidays. A business day count from WEEKDAY alone includes bank holidays, so add a holiday flag in the Calendar table.
- Store the weekend flag in the Calendar table instead of recalculating it in each measure. Filters on a stored column are faster and easier to audit.
- WEEKDAY on a blank returns a blank, so check fact tables for missing dates.
In business terms
WEEKDAY supports payment and payroll calendars, cash forecasting by business day, bank cut-off planning and the business day counts used in accrual and interest calculations. Any schedule that must avoid weekends depends on a weekday flag.