# Excel – Formula for Current Roster Week

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

1. Open Excel File
Open the Excel file where you want to track the roster week.
2. Create a Cell for Current Roster Week
Select a cell where you want the current roster week to be displayed (e.g., E1).
3. 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")`

4. 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”.