
Got any Excel Questions? Free Excel Help .
When finding the weekday associated with any date, most of us would rather see it returned as a name of the day, rather than as a number (the default).
Here we show how to extract the weekday of any date by using the WEEKDAY function. By default, the day is given as a whole number, ranging from 1 (Sunday) to 7 (Saturday). However, this is often meaningless, and we usually would rather see the weekday returned as a name, such as Monday/Mon, Tuesday/Tue, and so on.
Before getting to the name of the day, we'll need to begin by extracting the number of the weekday. Here's the syntax for the WEEKDAY formula:
WEEKDAY(serial_number,return_type)
Lets say we want to return the weekday number of 31Jul2007 The serial_num is any valid date in this case 31Jul2007 and the return_type is a number that refers to the type of return value. Depending on what result you are looking for will determine the return_type that you will use:
return_type  Day of Week 

1 or omitted 
Numbers 1 (Sunday) through to 7 (Saturday) 
2  Numbers 1 (Monday) through 7 (Sunday) 
3  Numbers 0 (Monday) through 6 (Sunday) 
We will use the default function by omitting the returntype. Click
in cell A1 and type in a valid Excel date, such as 31Jul2007 (which is a
Tuesday). Then, click in cell B1 and enter the following formula:
=WEEKDAY(A1)
This formula will return the number 3, which equates to Tuesday, which is the day of the week that 31 July 2007 is.
An alternative would be to hardcode the date like this:
=WEEKDAY("31 Jul 2007”)
Remember, the WEEKDAY formula shown in the previous section only returns the weekday as a number. There are at least two ways we can use formulas to force Excel to show the actual name of the weekday.
The first method is perhaps the simplest, and all you need to do is apply a
custom number format of DDD or DDDD. Again using 31Jul2007 as an example,
select the date cell, rightclick, go to Format Cells, and then choose Number
tab>Custom. Enter in the custom format DDD under Type and click OK. You will get
Tue in your cell.
Another, probably safer way is to reference the date cell (e.g.,
=A1) and format this cell with a custom number format of DDD or DDDD. The big
advantage to this method is that it leaves our true underlying date as a valid
Excel date.
If you won't be using the weekday that is returned in any further calculations, you can use either of the three formulas that follow (TEXT, WEEKDAY with CHOOSE) to return the weekday of a date as text.
This formula assumes you have a valid date of 31Jul2007 n cell
A1:
=TEXT(A1,"DDDD")
will produce Tuesday, or you could hard code your formula like:
=TEXT("31 Jul 2007","DDDD")
you could use the slightly longer CHOOSE function to get the same result:
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
and finally, you can hard code the date in the CHOOSE and nest the WEEKDAY function within it like:
=CHOOSE(WEEKDAY("31 July 2007"),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") ")
All of these formulas will return the same result  Tuesday, remember though the underlying value of your cell will still be 31Jul2007, even though your cell reads Tuesday.
Got any Excel Questions? Free Excel Help . See Also: Day of Week  Week number  Calculate a Persons Age  Excel Formulas Tips Tricks etc  Excel Calendar for Valid Dates
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates