The simplest Excel formula to get the weekday from a date is =WEEKDAY(date). For example, if cell A1 contains 15-Jul-2024, =WEEKDAY(A1) returns 2 (Monday), because WEEKDAY defaults to Sunday=1. To return the weekday name instead of a number, use =TEXT(A1,"dddd") or =TEXT(A1,"ddd") for the abbreviated name.
Understanding the WEEKDAY Function
The WEEKDAY function is the core tool for weekday calculations in Excel. Its syntax is =WEEKDAY(serial_number, [return_type]). The first argument is the date (either a cell reference or a date entered with DATE(), like =WEEKDAY(DATE(2024,7,15))). The optional return_type controls how the numbers map to days:
- 1 (default): Sunday=1, Monday=2, … Saturday=7.
- 2: Monday=1, Tuesday=2, … Sunday=7 (common in many regions).
- 3: Monday=0, Tuesday=1, … Sunday=6.
- 11-17: For Excel 2010 and later; 11 makes Monday=1, 12 makes Tuesday=1, etc. These are useful when your week starts on a specific day.
For example, =WEEKDAY(A1,2) on 15-Jul-2024 returns 1 (Monday), which is often more intuitive for business weeks. Choose the return_type that matches how your organization defines the start of the week.
Returning the Weekday Name (Text)
If you need the actual day name (e.g., “Monday”) rather than a number, use the TEXT function. The syntax is =TEXT(date, "dddd") for the full name, or =TEXT(date, "ddd") for the three-letter abbreviation. For instance, with A1 containing 15-Jul-2024:
=TEXT(A1,"dddd")returns “Monday”.=TEXT(A1,"ddd")returns “Mon”.
You can also combine TEXT with other functions, such as =TEXT(A1,"dddd, mmmm d") to get “Monday, July 15”. Note that TEXT returns text, so you cannot use the result in further date arithmetic without converting it back.
Choosing Between WEEKDAY and TEXT
Which function you use depends on your goal. Use WEEKDAY when you need a numeric value for calculations—like counting weekdays, finding the next occurrence of a day, or sorting by day of week. Use TEXT when you want a human-readable label for reports or charts. A common pattern is to use WEEKDAY in a helper column for filtering, then display the name with TEXT elsewhere. For example, to count how many Mondays are in a date range, you might use =SUMPRODUCT(--(WEEKDAY(A1:A10,2)=1)).
Common Problems and Solutions
One frequent issue is that dates entered as text (like “July 15, 2024”) are not recognized as dates, causing WEEKDAY to return a #VALUE! error. Fix by converting text to dates using DATEVALUE, or by ensuring cells are formatted as dates. Another problem: the return_type defaults to 1, so if you expect Monday=1 but get 2, you forgot to specify return_type 2. Also, remember that WEEKDAY returns a number, so if you see a date-like result (e.g., 2/1/1900), the cell is formatted as a date—change the number format to General.
Alternatives: CHOOSE and SWITCH
If you need a custom weekday label (e.g., “Mon” or “Weekday”), you can combine WEEKDAY with CHOOSE. For example, =CHOOSE(WEEKDAY(A1,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun") returns the abbreviated name. In Microsoft 365 or Excel 2021+, you could use SWITCH: =SWITCH(WEEKDAY(A1,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun"). These are more flexible than TEXT if you need non-standard names or languages.
Working with Weekends and Workdays
Often the goal is to identify weekends or count workdays. To check if a date is a weekend, use =OR(WEEKDAY(A1,2)>=6) (since in return_type 2, Saturday=6 and Sunday=7). For counting workdays between two dates, use =NETWORKDAYS(start_date, end_date) or =NETWORKDAYS.INTL for custom weekends. For example, =NETWORKDAYS(DATE(2024,7,1),DATE(2024,7,31)) returns 23 weekdays in July 2024 (assuming no holidays). To find the next Monday after a date, use =A1+MOD(8-WEEKDAY(A1,2),7)—this adds the number of days until the next Monday.
Frequently Asked Questions
How do I get the weekday as a number with Monday=1? Use =WEEKDAY(A1,2). This is the most common return_type for international users who expect Monday as the first day.
Why does my WEEKDAY formula return a date instead of a number? The cell is formatted as a date. Select the cell, go to Home > Number Format > General, and it will display the number.
Can I use WEEKDAY with a date stored as text? Yes, but first convert it with =WEEKDAY(DATEVALUE("2024-07-15")) or ensure the cell is a real date. If the text is in a recognized format, DATEVALUE will parse it.
What is the difference between WEEKDAY and TEXT for weekday? WEEKDAY returns a number; TEXT returns a string. Use WEEKDAY for calculations, TEXT for display.
How do I highlight weekends in conditional formatting? Use a formula like =OR(WEEKDAY($A1,2)>=6) in a conditional formatting rule applied to your range. This will highlight Saturdays and Sundays.
Does WEEKDAY work with dates before 1900? Excel’s date system starts at January 1, 1900. Dates before that are not supported; you’ll get a #VALUE! error. For historical dates, you may need to use alternative methods.
Practical Example: Weekly Report
Suppose you have a list of sales dates in column A (A2:A100) and amounts in column B. To sum sales by weekday, create a helper column C with =WEEKDAY(A2,2), then use =SUMIF(C:C,1,B:B) for Monday, =SUMIF(C:C,2,B:B) for Tuesday, etc. This gives you a quick breakdown of sales by day of week.
Related
- Excel Flexi Timesheet Template UK: How to Build One
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Protected Sheet Won’t Let You Sort? FixesExcel usually blocks sorting on a protected sheet because sorting is not allowed in the sheet’s protection settings,…
- Excel Average by Range: Formula & ExamplesTo average a range of cells in Excel, use the AVERAGE function: =AVERAGE(A1:A10) averages all numeric values in…
- How to Compare Hours in Excel With Formulas and FormattingTo compare two times in Excel, enter them as real time values and use a comparison formula such…
- Excel XLOOKUP Practice Examples: Formulas and TipsExcel XLOOKUP practice examples are easiest to learn by matching a lookup value to a column and returning…
- Search Excel Cells for Specific Text: Methods and FormulasTo search Excel for specific text, press Ctrl+F on Windows (Command+F on Mac), type the text, and choose…
- Combine Cells in Excel for Names: Formulas and StepsTo combine a first name in A2 and a last name in B2, enter =A2&" "&B2 in C2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.