Here’s how you can use Excel to calculate how many weeks it has been since a certain start date:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
We have a scenario where we want to determine what week an employee is in relative to his job, or what week a student is in for their course. We also want an indicator to tell us when he is in his 15th week. This employee started on March 14, 2014:
To determine what week Tony is in, we will use the WEEKNUM and TODAY functions. Basically, we will subtract the week number of his start date from the week number of today. This way, each time the TODAY function updates, it will provide the appropriate number of weeks.
Our formula will be: =WEEKNUM(TODAY(),2)-WEEKNUM(G6,2)
In the WEEKNUM function, the second factor of the syntax indicates what the starting day of the week is:
Next, to alert us when Tony is in his 15th week, we will use Conditional Formatting, found in the Styles group on the Home tab. Select “New Rule” then “Use a formula to determine which cells to format” and enter the formula: =H6>14:
Then set the format to whatever you’d like. I chose a white text and a red fill.
Since I can’t move Today forward, let’s pretend that Tony started on January 12th. Here’s the result:
Since the week number exceeded 14, Conditional formatting changed the cell to the formatting I indicated.