MS Excel: WEEKDAY Function (WS, VBA)
In Microsoft Excel, the WEEKDAY function returns a number representing the day of the week, given a date value.
The syntax for the WEEKDAY function is:
WEEKDAY( serial_number, [return_value] )
serial_number is a date expressed as a serial number or a date in quotation marks.
return_value is optional. It is the option used to display the result. It can be any of the following values:
|1||Returns a number from 1 (Sunday) to 7 (Saturday). This is the default if parameter is omitted.|
|2||Returns a number from 1 (Monday) to 7 (Sunday).|
|3||Returns a number from 0 (Monday) to 6 (Sunday).|
If this parameter is omitted, the WEEKDAY function assumes that the return_value is set to 1.
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- Worksheet function (WS)
- VBA function (VBA)
Worksheet Function Example
Let's take a look at an example to see how you would use the WEEKDAY function in a worksheet:
Based on the Excel spreadsheet above, the WEEKDAY function would return the following:
|=WEEKDAY(A1)||would return 1|
|=WEEKDAY(A1, 1)||would return 1|
|=WEEKDAY(A1, 2)||would return 7|
|=WEEKDAY(A1, 3)||would return 6|
|=WEEKDAY(A2)||would return 5|
|=WEEKDAY(A3)||would return 6|
|=WEEKDAY(38157)||would return 7|
|=WEEKDAY("July 12, 2004")||would return 2|
VBA Function Example
The WEEKDAY function can also be used in VBA code. For example:
Dim LWeekday As Integer LWeekday = Weekday("12/31/2001", vbSunday)
In this example, the variable called LWeekday would now contain the value of 2.
Frequently Asked Questions
Question: Is there a LIKE function in Excel similar to the one in Access? I'm trying to write a formula equivalent to the following:
=if(D14 like "*Saturday*", Now()+2, Now()+1)
Where cell D14 is a date value formatted as Saturday, August 27, 2005.
Answer: Since your value in cell D14 is a date value, you can use the WEEKDAY function to determine which day of the week it is. In this case, you are looking for a Saturday. The WEEKDAY function will return a value of 7 when the date falls on a Saturday.
Try using the following formula: