Return The Weekday of Date in Excel

< Back to Search results

 Category: [Excel]  Demo Available 

Return The Weekday of Date in Excel


Excel: Return The Weekday of Date in Excel/Return The Day of Week of a Date

Got any Excel/VBA Questions? Free Excel Help.


Excel: Returning Weekday 

To return the weekday of any date in Excel we can use the WEEKDAY formula. The day is given as an whole number, ranging from 1 (Sunday) to 7 (Saturday), by default.

The formula syntax for the WEEKDAY formula is;


Serial_num is any valid date. See how Excel stores dates & times. 
Return_type is a number (between 1 and 3) that determines the type of return value. 1, or omitted = 1 (Sunday) through 7 (Saturday). 2 = 1 (Monday) through 7 (Sunday). 3 = 0 (Monday) through 6 (Sunday).

An example of WEEKNUM formula is as shown below


Where A1 houses a valid Excel date. 

To hard code the date we could use:

=WEEKDAY("3 Apr 2005",1)

Return Weekday as Weekday Name

The WEEKDAY formula shown above will return the Weekday as a number. However, that is often meaningless as being humans would rather see the the Weekday returned as a Weekday name, i.e. Monday/Mon, Tuesday/Tue etc. There are at least 2 ways we can achieve this. The first is perhaps the simplest and all you need to do is apply a custom number format of DDD or DDDD. That is, select the date cell and go to Format>Cells>Number - Custom. Another similar way is to reference the date cell (e.g. =A1) and format this cells with a custom number format of DDD or DDDD. The BIG advantage to this method is our true underlying date is left as a valid Excel date. 

Return Weekday as Weekday Text

If you will not be using the Weekday that is returned in any further calculations we can either of the 2 formulas below (TEXT and WEEKDAY with CHOOSE) to return the Weekday of a date.

=TEXT(A1,"DDDD") OR =TEXT("20 Dec 2005","DDDD")

=CHOOSE(WEEKDAY("4 Dec 2005"),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Got any Excel Questions? Free Excel Help.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

Examples & Usage Of Excel Database Functions
Excel Data Tables
Data Validation
Excel Date & Time Calculations
Examples & Usage Of Excel Date and Time Functions


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


stars (0 Reviews)