Working with dates in Excel often means figuring out what day of the week a date falls on, or calculating future weekdays. This list covers the ten most useful Excel functions for weekday-related tasks, from simple day names to complex scheduling formulas. Whether you’re building a project timeline, tracking attendance, or just trying to avoid weekend deliveries, these functions will save you time and prevent errors.
WEEKDAY Function
The WEEKDAY function returns a number representing the day of the week for a given date. By default, Sunday is 1 and Saturday is 7, but you can change the return_type argument to make Monday the start of the week (2) or use other numbering systems. This is the foundation for most weekday calculations, and it’s perfect for flagging weekends or grouping data by day.
TEXT Function
While not strictly a weekday function, TEXT can convert a date into a weekday name. Use the format code “dddd” to get the full day name (e.g., “Monday”) or “ddd” for the abbreviated version (e.g., “Mon”). This is ideal for creating readable reports or dashboards where numeric weekday values aren’t intuitive.
WORKDAY Function
WORKDAY returns the date that is a specified number of working days before or after a start date. It automatically excludes weekends and can also exclude a list of holidays you provide. This is essential for project planning, estimating delivery dates, or calculating deadlines that fall on business days only.
WORKDAY.INTL Function
WORKDAY.INTL is a more flexible version of WORKDAY, allowing you to define which days of the week are considered weekends. You can specify a weekend string like “SatSun” or use a numeric code for common patterns. This is invaluable for companies that operate on non-standard schedules, such as those with a Sunday-Thursday workweek.
NETWORKDAYS Function
NETWORKDAYS calculates the number of whole working days between two dates, excluding weekends and optionally a list of holidays. This is perfect for calculating employee tenure, project duration, or the number of business days available for a task. It’s the inverse of WORKDAY, giving you a count instead of a date.
NETWORKDAYS.INTL Function
NETWORKDAYS.INTL combines the flexibility of WORKDAY.INTL with the counting capability of NETWORKDAYS. You can specify custom weekends and holidays, making it adaptable to any work schedule. Use this when you need to count working days between dates for a team that doesn’t follow the traditional Monday-Friday pattern.
CHOOSE Function
CHOOSE is a versatile function that can turn a WEEKDAY result into a custom label. For example, you can use CHOOSE(WEEKDAY(date), “Sun”, “Mon”, “Tue”, …) to create your own day abbreviations or even translate them into another language. This is a lightweight alternative to TEXT when you need full control over the output.
IF Function with WEEKDAY
Combining IF with WEEKDAY allows you to create conditional logic based on the day of the week. For instance, you can check if a date is a weekend and then apply a different formula, such as adding a surcharge for weekend orders. This pattern is powerful for automating decisions in financial models or operational spreadsheets.
SUMPRODUCT with WEEKDAY
SUMPRODUCT can be used to count or sum values based on the weekday of a date range. For example, you can sum all sales that occurred on Mondays by using SUMPRODUCT((WEEKDAY(date_range)=2)*(sales_range)). This is an advanced technique that avoids array formulas and is great for analyzing weekly patterns in data.
EDATE with WORKDAY
EDATE shifts a date by a specified number of months, and when combined with WORKDAY, you can find the first working day of a month. For example, WORKDAY(EDATE(today,1)-1,1) returns the first business day of next month. This is useful for creating monthly schedules, billing cycles, or reporting deadlines that must fall on a business day.
How to Choose
The right function depends on your goal. If you just need to know the day name, use TEXT or WEEKDAY with CHOOSE. For counting working days between two dates, NETWORKDAYS is your go-to. If you need to calculate a future date excluding weekends, use WORKDAY. For non-standard weekends, switch to the .INTL versions. And for complex conditional logic, combine WEEKDAY with IF or SUMPRODUCT. Start with the simplest function that meets your needs, then layer in flexibility as required.
Frequently Asked Questions
Can I make Monday the first day of the week? Yes, set the return_type argument in WEEKDAY to 2, and Monday becomes 1, Sunday becomes 7.
How do I exclude holidays from WORKDAY? Add a range of holiday dates as the third argument in WORKDAY or WORKDAY.INTL.
What’s the difference between WORKDAY and NETWORKDAYS? WORKDAY returns a date, while NETWORKDAYS returns a count of days. Use WORKDAY to find an end date, and NETWORKDAYS to measure duration.
Can I use these functions in Google Sheets? Yes, all of these functions are available in Google Sheets with the same syntax, so you can apply the same logic.
Why does my WEEKDAY formula return a number instead of a name? WEEKDAY is designed to return a number. To get a name, wrap it in TEXT or use CHOOSE to map numbers to names.
Final Thoughts
Mastering these weekday functions will make your date calculations more accurate and efficient. Start with WEEKDAY and TEXT to get comfortable with day-of-week logic, then explore WORKDAY and NETWORKDAYS for scheduling. With these tools, you can handle most weekday-related scenarios in Excel with confidence.