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:
As you can see, there is a format style called "Data Bar" that will do exactly what we want.
Fun Statistics
In addition to the visual bar, I wanted an easy way to see some fun statistics about the semester such as hours until Christmas, Monday's survived, days until the next holiday, and a lot of other information.
You can look on the excel file yourself for what all of the formulas are, but I'd like to go more in depth on a few of them. One in particular was challenging, which calculates the number of home or away football games left in the fall. I couldn't find a formula that could (1) count the number of values in a range based on (2) the date in a different range, compared to today's date, (3) and only count if the text in yet another range was "Home". So I made a column in the table that would cheat a little bit, and would do everything for me except count the value.
=IF(AND(TODAY()<N18,Q18="Home"),1,"")
The above formula will output a 1 if it is a home game that hasn't occurred yet, and will make the cell blank if it is not a home game or if the game has already happened. Then in my "Statistics" section, the formula that counts home games left is just
=SUM(CheatColumn1:CheatColumn12)
I was able to use a built in formula for counting the total number of football games left. The "COUNTIF" function is able to count the instances where a certain criteria is true. In this case, I wanted it to increment if the date in a cell was greater then today. My dates were in the range N18:P33 (due to merged cells, normally it would just be N18:N33)
=COUNTIF(N18:P33,">"&TODAY())
If you have any other questions on the document or the download link doesn't work, send me an email or use the contact form on my website! Thanks for reading!
