Got any Excel Questions? Free Excel Help . See Also: Return Last Day of Month || Add/Take Months from a Date || Excel Formulas Tips Tricks etc || Excel Calendar for Valid Dates
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. See how
Excel stores dates &
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. See how Excel stores dates & times
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: Return Last Day of Month || Add/Take Months from a Date || 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 Add-ins 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 firstname.lastname@example.org 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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates