EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Return The Weekday of Date in Excel

| | Information Helpful? Why Not Donate.

 

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

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;

WEEKDAY(serial_number,return_type)

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

=WEEKDAY(A1)/WEEKDAY(A1,1)
OR
=WEEKDAY(A1,2)
OR
=WEEKDAY(A1,3)

Where A1 houses a valid Excel date. See how Excel stores dates & times .

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(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
OR
=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 special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

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