Excel Progress Bars

As my friends love to point out, I make an Excel document for everything. One fun excel chart that I started making is what I call a "progress bar" chart. It displays a progress bar for the current semester (or summer), along with some important events. You could also call it a timeline with a progress bar. I've posted pictures below about it, but you can download the file at the link below. I recommend opening it in Microsoft Excel on a computer/laptop, because it usually doesn't display correctly in Google sheets or on mobile.

.xlsx file hosted on Google Drive [100 Kb]: Download Link

Google sheets version: Webpage

The document has multiple sheets, for Spring 2017 through Fall 2017. The boxes above each bar are customized for my schedule, life, and work holidays. If you download the file you can change it to fit your own life.

Summer 2017:

Fall 2017:

For the fall semester progress bar I included each Clemson football game, color coded for whether it's a home or away game.

Here's an overview of the Summer progress bar

Formulas and Math

The area behind the picture with the weeks (in this case 1-17) is all one merged cell, with a single formula in it. That formula is:

=NETWORKDAYS(StartDate,NOW())/(NETWORKDAYS(StartDate,EndDate))

Note: the function "NETWORKDAYS" calculates the number of workdays between two dates, for a 5-day work week.

This formula basically just divides the number of workdays that have elapsed so far by the total number of workdays in a given period. In this case, when I write this it is Tuesday of the 4th week on the progress bar. So we have 3*5+2 days, or 17 days. The second half of the formula is the total number of days in the range, which is 5*17 or 85 days. 17/85=0.2 or 20% (displayed to the left of the progress bar)

Now that the large merged cell has a value in it that will be between 0 and 1, I used conditional formatting to have it simulate a "progress bar". I applied a new rule using the following settings: