Function Problems

Calculate an Eight-Week Training Number in Excel from a Monday Start Date

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Calculate a training week number from a Monday start date and return a blank outside an eight-week training period.

Calculate an Eight-Week Training Number in Excel from a Monday Start Date
Product
Microsoft Excel and WPS Spreadsheets
Device & OS
not provided
Scenario
A spreadsheet contains a training start date and a date to evaluate for each person.
Observed behavior
The desired result is a week number from 1 to 8 during training and a blank before the start date or after the training period.
Before you start

Enter a Monday start date in A2 and the date to evaluate in B2. Make sure both cells contain valid spreadsheet dates rather than text.

Solution 1Recommended

Calculate the Training Week from the Start and Evaluation Dates

Use this formula when each training period starts on a Monday and lasts eight weeks.

1
Enter the dates

Enter the Monday training start date in A2 and the date you want to evaluate in B2.

2
Add the formula

Select C2 and enter =IF(OR(A2="",B2="",B2<A2,B2>=A2+56),"",INT((B2-A2)/7)+1).

3
Apply it to other rows

Drag the fill handle from C2 down the column to calculate the training week for additional people or dates.

Calculate the Training Week from the Start and Evaluation Dates
How the eight-week limit works: Week 1 begins on the start date, and each following Monday starts a new week. The formula returns a blank for missing dates, dates before the start, and dates 56 or more days after the start.
Calculate training weeks in WPS

Track Training Weeks in WPS Spreadsheets

Use WPS Spreadsheets to calculate weeks 1–8 from a Monday start date and leave the result blank outside the training period.

  1. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the dates: Enter the Monday start date in A2 and the date to evaluate in B2.
  3. 3. Calculate the week: Select C2 and enter =IF(OR(A2="",B2="",B2<A2,B2>=A2+56),"",INT((B2-A2)/7)+1).
  4. 4. Copy the formula: Drag the fill handle down from C2 to apply the calculation to additional rows.
Calculate training weeks with a single formula.Open and edit Microsoft Excel XLSX and XLS files.Fill the formula down for multiple training records.
QA img-9

Frequently Asked Questions

How do I calculate the current training week without entering a date in B2?

With the Monday start date in A2, enter =IF(OR(A2="",TODAY()<A2,TODAY()>=A2+56),"",INT((TODAY()-A2)/7)+1). The result updates based on the current date.

What happens on the first day after the eight-week period?

The formula returns a blank when the evaluation date is 56 days or more after the start date. The last day of Week 8 is 55 days after the start.

How can I check whether the start date is a Monday?

With a date in A2, enter =WEEKDAY(A2,2)=1 in another cell. TRUE means the date is a Monday.

Why does the formula return a blank?

Check A2 and B2: the formula returns a blank if either date is missing, if B2 is before A2, or if B2 is at least 56 days after A2. Also make sure the entries are valid spreadsheet dates.