Excel Formula for Weekday Based on Date

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.