Excel can be used to manage team rosters – and can automate complex arrangements such as a two-weekly rotation.
This guide shows how to use a formula in Microsoft Excel to display the current roster week, alternating between “Week 1” and “Week 2”.
In this example – we will assume that Week 1 started with 1 January 2024, and that each week starts on a Monday.
Example Team Roster
Current Roster Week: [Current Roaster Week – Week 1 or Week 2]
Week 1
| Name | Mon | Tue | Wed | Thur | Fri |
|---|---|---|---|---|---|
| Alex | WFH | WFH | WFH | Office | Office |
| Blake | Office | Office | Office | Office | WFH |
| Casey | Office | Office | Office | WFH | WFH |
Week 2
| Name | Mon | Tue | Wed | Thur | Fri |
|---|---|---|---|---|---|
| Alex | WFH | WFH | Office | Office | Office |
| Blake | Office | Office | Office | Office | WFH |
| Casey | Office | Office | WFH | WFH | WFH |
Steps
- Open Excel File
Open the Excel file where you want to track the roster week. - Create a Cell for Current Roster Week
Select a cell where you want the current roster week to be displayed (e.g., E1). - Enter the Formula
In the selected cell, enter the following formula:=IF(MOD(ROUNDDOWN((TODAY()-DATE(2024,1,1))/7,0),2)=0,"Week 1","Week 2")
- Apply and Verify
Press Enter. The cell should now display either “Week 1” or “Week 2” based on the current date.
How does the formula work?
- TODAY(): Returns today’s date.
- DATE(2024,1,1): Specifies the start date (1 January 2024).
- (TODAY() – DATE(2024,1,1)): Calculates the number of days since the start date.
- /7: Converts days to weeks.
- ROUNDDOWN(…, 0): Rounds down to the nearest whole number.
- MOD(…, 2): Returns the remainder when divided by 2 (alternates between 0 and 1).
- IF(…, “Week 1”, “Week 2”): Displays “Week 1” if the remainder is 0, otherwise “Week 2”.