Description
Use this function to determine the day of the week for that a date will fall on. Supported in Chains.
This returns an integer between 1 and 7 inclusive.
Syntax
WEEKDAY(serial_number,[return_type])
Inputs
This function has the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
serial_number |
Yes | A sequential number that represents the date of the day you are trying to find. | Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2024,6,23) for June 23, 2024. Problems can occur if dates are entered as text. |
return_type |
No | An integer that determines the type of return value. (See Return Type below.) | This can be a fixed number, a cell reference, or the product of a formula. |
Example
Return type
The WEEKDAY function allows you to specify what coding is to be used to determine the returned value.
| Return_type value | Number returned |
|---|---|
| 1 (default) | 1 = Sunday through 7 = Saturday. Behaves like previous versions of Microsoft Excel. |
| 2 | 1 = Monday through 7 = Sunday. |
| 3 | 0 = Monday through 6 = Sunday. |
| 11 | 1 = Monday through 7 = Sunday. |
| 12 | 1 = Tuesday through 7 = Monday. |
| 13 | 1 = Wednesday through 7 = Tuesday. |
| 14 | 1 = Thursday through 7 = Wednesday. |
| 15 | 1 = Friday through 7 = Thursday. |
| 16 | 1 = Saturday through 7 = Friday. |
| 17 | 1 = Sunday through 7 =Saturday. |
The second way to configure weekends is to provide a 7 character text string enclosed in quotation marks composed of 1s and 0s, where "1" indicates a non-work day and "0" indicates a work day. The first position represents Monday.
Sample data
| A | B | C | |
|---|---|---|---|
| 1 | Date | Person | Expenses |
| 2 | 2/1/2023 | Abby | $3456.00 |
| 3 | 5/12/2023 | Brandon | $4783.30 |
| 4 | 7/4/2023 | Corinna | $3167.09 |
| 5 | 2/21/2023 | Damon | $2965.89 |
| 6 | 12/5/2023 | Esther | $3014.88 |
| 7 | 6/16/2023 | Francoise | $2762.93 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Return the day of the week for a date in a cell (default numbering: Sunday=1 … Saturday=7). | =WEEKDAY(A2) |
A2 is 2/1/2023, a Wednesday. With the default return_type (1), Sunday=1 through Saturday=7, so Wednesday returns 4. |
| Return the day of the week using an alternate numbering scheme (Monday=1 … Sunday=7). | =WEEKDAY(A3, 2) |
A3 is 5/12/2023, a Friday. return_type 2 numbers Monday=1 through Sunday=7, so Friday returns 6. |
| Check whether a date falls on a weekend. | =IF(OR(WEEKDAY(A4,2)=6,WEEKDAY(A4,2)=7),"Weekend","Weekday") |
Using return_type 2 (Monday=1…Sunday=7), Saturday=6 and Sunday=7. A4 is 7/4/2023, a Tuesday (WEEKDAY(A4,2)=2), so neither condition is true and the formula returns "Weekday". |
| Total expenses for transactions that occurred on a Friday. | =SUM(IF(WEEKDAY(A2:A7)=6,C2:C7,0)) |
This formula works in the following manner:
For this data set, the formula returns: $7,546.23 (the sum of rows 3 and 7). |
| Look up the name of the day of the week for a date. | =CHOOSE(WEEKDAY(A5), "Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") |
WEEKDAY(A5) uses the default numbering, so it returns the position in the list to pick with CHOOSE. A5 is 2/21/2023, a Tuesday, so WEEKDAY(A5) = 3 and CHOOSE returns "Tuesday". |
Notes
- The WEEKDAY function will return a value even when the date is empty. Take care to trap this result if blank dates are possible.
- The Workiva platform stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 = 1.
- If
serial_numberis out of range, a #NUM! error is returned. - If the
return_typevalue isn't in the set specified above, a #NUM! error is returned. - Wildcards don't work with this function.