Day of the week from date in Excel

Image of calendar

Purpose: to retrieve the weekday number or weekday name (day of the week as text) from a date in Excel.

Examples of dates converted to weekday names and weekday numbers

We’ve brought these two topics together, to retrieve the weekday number or name, but you’ll see that they use different Excel functions – WEEKDAY() and TEXT().

Links to topics on this page

Get the weekday number from a date in Excel

Weekday() syntax

= WEEKDAY (serial_number, [return_type])

  • Serial number:
    • date to find the weekday from;
    • according to Microsoft Excel support feed the date directly into the WEEKDAY() function using the DATE() function, because errors can occur with dates entered as text. Dates can also be entered into WEEKDAY() via a cell reference, bringing in a date entered into another cell. See examples below.
  • 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() examples

= 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.

WEEKDAY() errors

Showing WEEKDAY function error in Excel

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.

Pages: 1 2