WEEKDAY and WORKDAY in Excel - Step-by-Step Guide

Introduction

These two functions help you work with dates easily. WEEKDAY tells you the day of the week for any date. WORKDAY helps you find a date after adding working days, skipping weekends and holidays.

When you want to know if a date falls on a Monday, Tuesday, or any other day.

When planning a project and you need to find the end date after a certain number of workdays.

When calculating deadlines that skip weekends and holidays.

When you want to highlight weekends or weekdays in a calendar.

When scheduling tasks only on business days.

Steps

Step 1: Click

- a blank cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula appears in the cell and formula bar

💡 For WEEKDAY, type =WEEKDAY(date) where date is a cell with a date or a date in quotes like "2024-06-01"

Step 3: Press

- Enter key

The cell shows a number from 1 to 7 representing the day of the week

Step 4: Type

- the formula bar

The formula appears in the cell and formula bar

💡 For WORKDAY, type =WORKDAY(start_date, days, [holidays]) where start_date is the starting date, days is how many workdays to add, and holidays is an optional range of dates to skip

Step 5: Press

- Enter key

The cell shows the date after adding the workdays, skipping weekends and holidays

Before vs After

Before

Cell A1 has date 2024-06-01 (Saturday), no formula

After

Cell B1 with =WEEKDAY(A1) shows 7 (Saturday), Cell C1 with =WORKDAY(A1, 3) shows 2024-06-06 (Thursday) skipping weekend

Settings Reference

Return type for WEEKDAY

📍 Second argument in WEEKDAY function, e.g., =WEEKDAY(date, return_type)

Controls which day is counted as the first day of the week

Default: 1

Holidays argument in WORKDAY

📍 Third argument in WORKDAY function, e.g., =WORKDAY(start_date, days, holidays)

Dates to exclude from workdays besides weekends

Default: Empty

Common Mistakes

Using WORKDAY without considering holidays

The result may include holidays as workdays, giving wrong dates

Add a holiday range as the third argument to skip those dates

Confusing WEEKDAY return types

You might think Monday is 1 but get Sunday as 1 instead

Specify the return_type argument to match your week start preference

Entering dates as text without proper format

Excel may not recognize the date and formulas give errors

Enter dates using date format or use DATE function like =DATE(2024,6,1)

Summary

WEEKDAY returns a number for the day of the week from a date.

WORKDAY calculates a future or past date counting only workdays, skipping weekends and holidays.

Remember to use the optional arguments to customize results for your needs.