How to Use WEEKDAY Function in Excel - Practical Guide

Learn to use the WEEKDAY function to identify which day of the week a date falls on, returning a number (1-7). This essential formula helps organize schedules, automate reporting, filter weekends, and create dynamic dashboards that respond to calendar patterns.

Why This Matters

Professionals use WEEKDAY to automate scheduling, filter business days, and create intelligent workflows without manual date checking.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Knowledge of date formatting and date values in Excel

Step-by-Step Instructions

Open Excel and select target cell

Click the cell where you want the result to appear, for example C2.

Type the WEEKDAY formula

Enter =WEEKDAY(A2) where A2 contains your date. This returns 1 for Sunday through 7 for Saturday by default.

Press Enter to confirm

Hit Enter to execute the formula and see the numeric result (1-7) for the corresponding date.

Modify return type with second parameter (optional)

Use =WEEKDAY(A2,2) to return 1-7 as Monday-Sunday, or =WEEKDAY(A2,3) for 0-6 Monday-Sunday format.

Copy formula down to apply to multiple rows

Select cell C2, copy (Ctrl+C), highlight range C3:C100, and paste (Ctrl+V) to apply to all dates.

Alternative Methods

Using TEXT function for day names

Use =TEXT(A2,"dddd") to directly display the day name instead of a number. This is more readable but less flexible for calculations.

Combining WEEKDAY with IF for conditional logic

Use =IF(WEEKDAY(A2)>5,"Weekend","Weekday") to automatically categorize dates as weekend or workday.

Tips & Tricks

✓Use mode 2 (Monday=1) for business contexts to easily identify weekdays with values 1-5.
✓Combine WEEKDAY with SUMIF to sum values only for specific days of the week.
✓Remember: mode 1 (default) starts with Sunday; mode 2 starts with Monday.

Pro Tips

★Nest WEEKDAY inside CHOOSE to directly return day names: =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
★Use WEEKDAY in FILTER functions to dynamically extract only weekday or weekend data from large datasets.
★Combine with SUMPRODUCT to count specific weekdays in a date range for advanced scheduling analysis.

Troubleshooting

WEEKDAY returns #VALUE! error

This occurs when the cell contains text instead of a date. Convert text to a date using VALUE() or DATE() function, or ensure the source data is properly formatted as a date.

Results don't match expected days

Check your mode parameter; mode 1 starts with Sunday=1, mode 2 with Monday=1, and mode 3 with Monday=0. Verify which system matches your expectation.

Formula won't copy to other cells

Ensure cell references use relative references (A2) not absolute ($A$2) so they adjust when copied down. Use absolute references only for lookup tables.

Related Excel Formulas

TEXT DATE TODAY IF CHOOSE SUMPRODUCT FILTER

Frequently Asked Questions

What do the numbers 1-7 represent in WEEKDAY?

By default (Mode 1), 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday. Using Mode 2 shifts to 1=Monday through 7=Sunday, better for business use.

Can I display the day name instead of a number?

Yes, use TEXT(A2,"dddd") for full names or "ddd" for abbreviations. Alternatively, nest WEEKDAY in CHOOSE to map numbers to custom day names.

How do I identify weekends using WEEKDAY?

Use =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") with Mode 2, where days 6-7 are Saturday and Sunday. Or with Mode 1, use IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday").

Does WEEKDAY work with today's date?

Yes, use =WEEKDAY(TODAY()) to find the current day of the week as a number, perfect for creating dynamic schedules.