Determining How Many Weeks In #Excel

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:

wn1

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)

wn2'

In the WEEKNUM function, the second factor of the syntax indicates what the starting day of the week is:

wn3

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:

wn4

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:

wn5

Since the week number exceeded 14, Conditional formatting changed the cell to the formatting I indicated.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply