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.
Syntax
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:
| Value | Explanation |
|---|---|
| 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.
Applies To
- 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:
=if(Weekday(D14)=7,Now()+2,Now()+1)