Returns the day of the week corresponding to a date. Converts a date to a number representing the day of the week, essential for scheduling, business day calculations, and weekend identification.
Master the fundamentals of Excel WEEKDAY function
The WEEKDAY function returns the day of the week as a number from a date serial number. The function supports three different numbering systems via the return_type parameter: default (Sunday=1), ISO (Monday=1), and zero-based (Monday=0). This flexibility makes WEEKDAY essential for scheduling, business day calculations, and weekend identification.
Supports three return_type options: 1 (default) = Sunday(1) to Saturday(7), 2 = Monday(1) to Sunday(7), 3 = Monday(0) to Sunday(6).
Enables easy identification of weekends and business days through simple numeric comparisons.
Works with Excel's date serial number system, accepting dates in various formats including serial numbers, date strings, and cell references.
Essential for scheduling systems, workday calculations, and date-based business logic that depends on day of week.
Function-specific parameters
Function-specific return type
Determine day of week for scheduling and planning purposes
Identify weekdays vs weekends for business logic and calculations
Detect weekends to apply different rules or formatting
Implement workday-only calculations and validations
Exact matching required
Returns numeric position
Handles missing text gracefully
=WEEKDAY(serial_number, return_type)The date for which you want to find the day of the week. Can be a date serial number, date string, or cell reference containing a date.
Optional. Number 1-3 that determines the type of return value. 1 (default) = Sunday(1) to Saturday(7), 2 = Monday(1) to Sunday(7), 3 = Monday(0) to Sunday(6)
The day of the week as a number (depends on return_type)
Description: Returns the day of the week for a given date
Get day of week using default return type
Returns 2 for Monday (default: 1=Sunday, 2=Monday, 3=Tuesday, etc.). The default return_type is 1.
Using WEEKDAY function in VBA
Sub WEEKDAYExample()
Dim result As Integer
Dim myDate As Date
myDate = DateValue("2024-01-15")
result = Weekday(myDate)
Range("A1").Value = result
MsgBox "WEEKDAY(2024-01-15) = " & result
End Sub
' Get day of week with return type
Sub GetWeekdayWithType()
Dim dateValue As Date
Dim dayNum As Integer
dateValue = Range("A1").Value
' Return type 2: Monday=1, Tuesday=2, ..., Sunday=7
dayNum = Weekday(dateValue, vbMonday)
Range("B1").Value = dayNum
End Sub
' Check if weekend
Sub CheckWeekend()
Dim dateValue As Date
Dim dayNum As Integer
Dim isWeekend As Boolean
dateValue = Range("A1").Value
dayNum = Weekday(dateValue, vbMonday)
isWeekend = (dayNum > 5)
Range("B1").Value = IIf(isWeekend, "Weekend", "Weekday")
End Sub
' Loop through dates and get weekdays
Sub GetWeekdaysFromRange()
Dim cell As Range
Dim dayNum As Integer
For Each cell In Range("A1:A10")
If IsDate(cell.Value) Then
dayNum = Weekday(cell.Value, vbMonday)
cell.Offset(0, 1).Value = dayNum
End If
Next cell
End SubIdentify weekends from dates
Apply business rules based on day of week
Convert day number to day name
Calculate next weekday from a date
WEEKDAY returns #VALUE! error
=WEEKDAY("invalid date")Solution: Ensure the first argument is a valid date. Check that the cell contains a date serial number, not text. Use DATEVALUE() to convert text dates, or verify cell formatting is set to Date.
WEEKDAY returns unexpected day numbers
=WEEKDAY(A1)Solution: Check the return_type parameter. Default (1) gives Sunday=1, Monday=2. Return_type 2 gives Monday=1, Tuesday=2. Return_type 3 gives Monday=0, Tuesday=1. Verify which numbering system you need for your application.
WEEKDAY returns wrong day
=WEEKDAY(A1, 2)Solution: Verify the input date is correct. Excel dates are stored as serial numbers, so text dates may be misinterpreted. Ensure dates are valid and formatted correctly. Check your system date settings if dates seem shifted.