Week-Based Calculations Excel Practice Questions with Solutions

Introduction

Week-based calculations are useful when working with attendance, sales, employee schedules, appointments, project timelines, and weekly reports. Excel provides functions such as WEEKDAY and WEEKNUM to identify the day of the week and the week number of a date. In this chapter, you will practice these functions through different examples, starting with simple weekday extraction and moving toward practical weekly calculations. Week-Based Calculations Excel Practice questions with solutions to help you understand the concepts.

Question 1: Find the Day Number Using WEEKDAY in Excel

Problem Statement

Find the weekday number for each date.

For this exercise, use Sunday as day 1 and Saturday as day 7.

Excel Data

Solution

In B2, enter:

=WEEKDAY(A2,1)

Copy the formula down.

Expected Output

The second argument 1 means:

• Sunday = 1
• Monday = 2
• Tuesday = 3
• Wednesday = 4
• Thursday = 5
• Friday = 6
• Saturday = 7

Question 2: Display the Weekday Name

Problem Statement

Display the actual day name for each date.

Excel Data

Solution

In B2, enter:

=TEXT(A2,"dddd")

Copy down.

Expected Output

For an abbreviated day name, use:

=TEXT(A2,"ddd")

For example, Monday becomes Mon.

Question 3: Find the Week Number Using WEEKNUM

Problem Statement

Find the week number for each date.

Use the standard Excel system where the week begins on Sunday.

Excel Data

Solution

In B2, enter:

=WEEKNUM(A2,1)

Copy down.

Expected Output

The second argument determines which day starts the week.

Question 4: Find the ISO Week Number

Problem Statement

Find the ISO week number for each date.

The ISO week system starts the week on Monday.

Excel Data

Solution

In B2, enter:

=ISOWEEKNUM(A2)

Copy down.

Expected Output

ISOWEEKNUM follows the ISO week-numbering system.

Question 5: Identify Weekday or Weekend

Problem Statement

Determine whether each date is a weekday or a weekend.

Treat Saturday and Sunday as weekends.

Excel Data

Solution

In B2, enter:

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Copy down.

Expected Output

Here, WEEKDAY(A2,2) gives:

• Monday = 1
• Tuesday = 2
• …
• Saturday = 6
• Sunday = 7

Therefore, values greater than 5 are weekends.

Question 6: Create a Weekly Sales Report

Problem Statement

A sales team records daily sales. Assign a week number to every sales transaction.

Excel Data

Solution

In D2, enter:

=WEEKNUM(A2,2)

Copy down.

Expected Output

Using 2 makes Monday the first day of the week.

Question 7: Create a Weekly Schedule Label

Problem Statement

Create a label such as:

Week 40 - Monday

for each date.

Excel Data

Solution

In B2, enter:

="Week "&WEEKNUM(A2,2)&" - "&TEXT(A2,"dddd")

Copy down.

Expected Output

This is useful for creating readable weekly reports.

Question 8: Identify the First and Last Day of the Week

Problem Statement

For each date, calculate the Monday and Sunday of the same week.

Excel Data

Solution

In B2, enter:

=A2-WEEKDAY(A2,2)+1

This returns the Monday of the same week.

In C2, enter:

=B2+6

This returns the Sunday of that week.

Copy both formulas down.

Expected Output

This technique is useful when you need weekly reporting periods.

Question 9: Classify Dates into Weekday Names

Problem Statement

Create a working schedule where Monday to Friday are marked as Workday and Saturday/Sunday are marked as Off.

Excel Data

Solution

In B2, enter:

=IF(WEEKDAY(A2,2)<=5,"Workday","Off")

Copy down.

Expected Output

Question 10: Create a Complete Weekly Analysis

Problem Statement

You are given employee attendance dates. Create three pieces of information:

1. Day name
2. Week number
3. Weekday/Weekend status

Excel Data

Solution

In C2, enter:

=TEXT(B2,"dddd")

In D2, enter:

=WEEKNUM(B2,2)

In E2, enter:

=IF(WEEKDAY(B2,2)>5,"Weekend","Weekday")

Copy all three formulas down.

Expected Output

This combines TEXT, WEEKNUM, WEEKDAY, and IF into a practical weekly-analysis exercise.

Key Takeaways

• WEEKDAY in Excel returns the day number for a date.
• WEEKDAY(A2,2) makes Monday equal to 1 and Sunday equal to 7.
• WEEKNUM returns the week number of a date.
• WEEKNUM(A2,2) uses Monday as the first day of the week.
• ISOWEEKNUM in excel follows the ISO week-numbering system.
• TEXT(A2,"dddd") returns the full weekday name.
• TEXT(A2,"ddd") returns the abbreviated weekday name.
• IF and WEEKDAY can be combined to identify weekdays and weekends.
• A week’s Monday can be calculated using A2-WEEKDAY(A2,2)+1.
• The Sunday of that week can then be calculated by adding 6 days.
• Week numbers are useful for weekly sales, attendance, project, and operational reports.
• Always choose the appropriate week-numbering system for your report because different systems can assign different week numbers around the beginning and end of a year.

FAQs

1. What does WEEKDAY do in Excel?

WEEKDAY returns a number representing the day of the week.

For example:

=WEEKDAY(A2,2)

returns 1 for Monday and 7 for Sunday.

2. What does WEEKNUM do in Excel?

WEEKNUM returns the week number for a particular date.

For example:

=WEEKNUM(A2,2)

returns the week number using Monday as the first day of the week.

3. What is the difference between WEEKDAY and WEEKNUM?

WEEKDAY identifies the day within a week.

WEEKNUM identifies the week within a year.

For example, a Monday can have a WEEKDAY value of 1 and a WEEKNUM value of 40.

4. How can I identify weekends in Excel?

Use:

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

With the 2 option, Saturday is 6 and Sunday is 7.

5. How can I find the Monday of a week?

Use:

=A2-WEEKDAY(A2,2)+1

This returns the Monday belonging to the same week as the date in A2.

6. How can I find the Sunday of the same week?

If the Monday of the week is in B2, use:

=B2+6

Alternatively:

=A2-WEEKDAY(A2,2)+7

7. What is ISOWEEKNUM in Excel?

ISOWEEKNUM returns the ISO week number for a date. ISO weeks start on Monday and follow specific rules for determining the first week of the year.

8. Why can WEEKNUM and ISOWEEKNUM return different results?

They can use different week-numbering rules. WEEKNUM allows different choices for the first day of the week, while ISOWEEKNUM follows the ISO week system.

9. How can I display the weekday name instead of a number?

Use the TEXT function:

=TEXT(A2,"dddd")

For a shorter name:

=TEXT(A2,"ddd")

10. Where are week-based calculations useful?

They are useful for attendance reports, weekly sales reports, employee schedules, project tracking, appointments, production reports, and other worksheets where data needs to be grouped or analyzed by week.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar