Purpose: to retrieve the weekday number or weekday name (day of the week as text) from a date in Excel.
We’ve brought these two topics together, to retrieve the weekday number or name, but you’ll see that they use different Excel functions –
Links to topics on this page
Get the weekday number from a date in Excel
= WEEKDAY (serial_number, [return_type])
- date to find the weekday from;
Return type(optional): there are about 10 different options with regard to the weekday numbering system returned, for example:
- 1 [the default value]: Numbers 1 (Sunday) through 7 (Saturday),
- 2: Numbers 1 (Monday) through 7 (Sunday). See the MS Excel support page for a list of all the options.
= WEEKDAY ( DATE (2020,4,26) , 2)
In the example above, the date Tuesday 26th April 2020 is provided to the WEEKDAY() function via the DATE() function.
Return type is set to ‘2‘, so that Monday is assigned the value ‘1’, through 7 to Sunday.
= WEEKDAY (A2)
WEEKDAY(A2) returns the weekday number for a date entered into cell A2.
Watch out for the fact that the WEEKDAY() function will return a value, even if the date is blank or invalid – it will not return an error message. For example, if we enter a date into the function as text but without speech marks
=WEEKDAY(12/25/2019), a value will be returned (7) which is not the correct value. The correct result is returned when the date is enclosed in speech marks =WEEKDAY(“25/12/2019”).
So make sure you use the correct format for entering values, and test your formula with dates where you know what the result should be.
This highlights the fact that in most other situations, Excel is usually very helpful in alerting us to when there’s an error.
>> On the next page we’re going to see how to return the day of the week as text from a date.