Time is a difficult concept to visualize. I always prefer looking at a visual representation of time instead of just words or numbers.
So here’s how you can make a simple progress bar that shows a visual representation of how much of the week has passed and how much is left to go.
Take a look at the spreadsheet itself. Every time you open or refresh the page the green progress bar will update to show you how much of the week has passed.
Most of the difficulty involved in making this has to do with the fact that we’re using the calendar week as the reference point that we’re measuring from, in this case the ISO week which runs from Monday to Sunday. Making an annual progress bar would be a lot simpler because you know that every year always starts on January 1st and ends on December 31st. So it’s simple to subtract today’s date from January 1st. But with a week progress bar you need to figure out the date of the most recent Monday and then subtract today’s date from that date.
Formulas in Use:
- SPARKLINE – This is what makes the mini bar graph itself. Time elapsed out of the total week.
- NOW – Outputs today’s date and the exact time. It will update every time the page is opened or refreshed
- ISOWEEKNUM – This returns the ISO week number that the current date falls into. It’s very common to think of the week in terms of the chunk of days Monday through Sunday but technically this is an arbitrary block of time to look at. Because of this we need to do some calculations in order to specify that we’re counting up from the most recent Monday.
- VLOOKUP – Looks for a certain value within a range of data and then returns whatever data is next to it. In this case we look up the ISO week number and then use that to get start date of that ISO week.
- Find the ISO week (Mon-Sun) number that today falls into.
This is the TODAY() function wrapped inside the ISOWEEKNUM() function.
In this case it will return the number 36 for the 36th week.
- From here I didn’t know of any way for Sheets to directly give me the start date of the given ISO week so I manually made a table that shows every ISO week number in 2019 and then the start and end date for each. This was a pain to make but I figure I’ll only have to do it once a year, I didn’t make it myself anyway, I just pasted in the values from a Google search. Using the VLOOKUP() function to search through the table looking for ’36’ (the current ISO week number), and then return the start and end dates for that week.
- Convert the start date into a date, time format
- Convert the end date into a date, time format
- Use the NOW function to show the current date and exact time
- Subtract the ISO start date in date,time format = time elapse from start of week to now.
Right now on Tuesday night at 11:28 pm I’m getting back 1.977823681 because at this time 1.977… days have passed since the most recent Monday at 12am.
- Use the SPARKLINE function to make an auto mini graph of the week. Reference the above cell containing the 1.977…. The max value that you enter in is 7 for the maximum number of days in a week.
Now your Week Progress bar is done, enjoy! I think this would be a useful thing to put at the top of a dashboard-type page where you’re tracking the projects you have on the go or money you’ve spent so far this month.