Date.DayOfWeek
Returns the day of the week of a date as a number, counted from the day you name as the first day of the week.
Date.DayOfWeek(dateTime as any, optional firstDayOfWeek as nullable Day.Type) as nullable numberReturns: A number from 0 to 6, or null when the input is null.
What it does
Date.DayOfWeek returns the weekday as a number. By default, Sunday is 0 and Saturday is 6. The firstDayOfWeek argument changes the count: with Day.Monday as the first day, Monday is 0 and Sunday is 6. That is the arrangement most finance teams want, because a weekend is then simply the last two numbers, 5 and 6.
Syntax and arguments
- dateTime: a date or a datetime value.
- firstDayOfWeek (optional): the day that counts as 0, such as Day.Monday. The default is Day.Sunday.
Example
Payments fall due on a date that is not a business day. A weekend due date is moved to the following Monday, as most supplier contracts require.
let
Due = #table(type table [Invoice = text, DueDate = date],
{{"INV1043", #date(2026, 10, 31)}, {"INV1044", #date(2026, 10, 29)}}),
WithDay = Table.AddColumn(Due, "Weekday", each Date.DayOfWeek([DueDate], Day.Monday), Int64.Type),
Adjusted = Table.AddColumn(WithDay, "PayDate", each
if [Weekday] = 5 then Date.AddDays([DueDate], 2)
else if [Weekday] = 6 then Date.AddDays([DueDate], 1)
else [DueDate], type date)
in
Adjusted
October 31, 2026 is a Saturday, so INV1043 has weekday 5 and moves two days, to Monday November 2. October 29 is a Thursday, so INV1044 keeps its date. The numbers 5 and 6 are the weekend when Monday is day 0.
Example: a second, different use
Count the business days between two dates by counting only the days whose weekday number is below 5.
Things to watch
- The default starts on Sunday. Always pass firstDayOfWeek so the weekend numbers mean what you expect.
- Holidays are not known to this function. A bank holiday needs its own list.
- The result is a number, so keep it as a number when you test it.
- Null in gives null out.
In business terms
Payment due dates, delivery dates and reporting deadlines often roll to the next business day. A weekday number lets the query apply that rule to every row in the same way.