Members-only tutorial

Watch the video and get the practice sheet with membership.

See membership options

Building a Year Progress Clone

About this Tutorial

Create a year progress bar to see how much of the year is gone. Great motivational chart to watch count up to 100%.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

EOMONTH() HOUR() MONTH() NOW() SPARKLINE() SWITCH() TODAY() WEEKDAY()

Video Transcript

<div>00:00 Hey, so welcome to Better Sheetz. Today I'm gonna do something interesting. If you haven't watched my videos before this, this might be a new one. 00:12 I'm gonna build a year Progress Clone , because I just was on u I was just on Twitter and on Twitter they started charging for the api. 00:22 And Year Progress is a Twitter account bot that I really like.

It, it helps me keep track of literally the percentage left in the year. 00:33 And I thought about it going away, and I was like, I really wanna know what the percentage of the year is left in. 00:42 And we can do something pretty cool here with Spark Line, I think, in Google Sheets.

But in addition to that, in this sort of announcement of Twitter charging for an API and then year progress, ultimately maybe going away they, they reference something. 01:00 So the Twitter bot is like for public consumption, but the person who created it actually also has a progress bar for the year, for the quarter, for the month, and even for the workday. 01:12 So I wanted to build out in Google Sheets this kind of thing, which I think could actually be very useful to you because I, I think it's gonna be very useful to me. 01:21 I already loved your progress on Twitter, and now I'll get it in Google Sheet.

The first thing is I've just created sheet.news. 01:30 So this is gonna be a very raw video. This is going to be working out the kinks and issues as we go. 01:38 But let me just go through what we want to do. We want to display a progress bar and progress and percentage, sorry, percentage of year left. 01:54 This is going to be essentially, you know, 365 days and however many days have gone by and of 365 days. 02:11 This is poor way of showing this <laugh> display a progress bar and percentage of your left.

Let's do something here. 02:19 I don't, I don't want to use this black and white text. I'm gonna use my free Google sheet. Add-on sheet styles. 02:26 You can go get it at add-ons. Get add-ons. I'm gonna show the sidebar here. Let's just change it to a little bit of a papa style. 02:35 I wanted a little, cause I don't know how long we're gonna be working on. This might be an hour <laugh>, no, it might be half an hour. 02:40 Who knows? Or maybe less. I don't know. I don't wanna stare at a black and white screen this whole time. 02:47 Little less con contrast here.

And you can just get that get add-ons, add-on extensions, add-ons, get add-ons, go to sheet style, and, and then type in sheet styles, and you'll be able to get it absolutely free. 03:00 All right, let's go back to our, what we need. We want this for year. We want this for quarter, month and maybe workday or day or a week maybe, maybe we won't do week. 03:16 I don't know how we're gonna do all this. We wanna show it in a progress bar . So we don't need a lot of stuff. 03:24 I'm gonna just duplicate this sheet. And I want to delete all this stuff. And we only need this.

We might add another line in between, but for now, we're just gonna give it a lot of space. 03:44 We're going to view show, great. We're not gonna see the grid lines. We're gonna cha we already changed this to Carla with the the click of the button in cheat styles. 03:54 So that's good for these. I want to two, I don't know if they should be, I don't, maybe this way aligned to the right and middle. 04:06 All right. What is the progress we wanna show? I mean, essentially we wanna do spark line, not spark life.

Almost like Spark life. 04:14 Our data is going to be let's do it in another se actually, we don't what we, we need, we need today. 04:24 Definitely. Let's do it in another cell. Cuz I, you might be wor wondering how I'm gonna do this math. Let's do it this way. 04:31 I know ultimately we wanna take the days spent divided by 365 by the number of days in a year. So what do we have? 04:47 We have today, but we don't wanna format it like a date.

We wanna format it like a number .  We don't need that extra dot. 04:55 So this is 4 49 62 and we know, actually let's find out what what January 1st is. It might be January 1st, or it might be some December 31st. 05:09 We have to figure out this in a hot second. Go back to format , number , date. Let's select not today. 1 1 20 23 what? 05:28 Zero one. There we go. Now let's see, what is that date format ? We don't even really need to know this number , but 44 9 27 could do. 05:44 Let's get the beginning of the year and the last date. So that'll be December 31st.

Cause I don't think this will be correct if we just take the difference of these. 05:59 We can do this is this minus this 360 4 I think we actually have to do. The year is going to be like December 31st until December 31st, right? 06:14 That's 365 days. So is it, this is a little confusing. One one until 1 1 24. Well, it's 365 days. That's all we care about. 06:33 And this is today. So we do today mine, no, yeah, today minus the first day of the month. That's 35. 06:45 Not first day of the month. First day of the year. So it's 35. It's been 35 days in this year. 06:50 Great.

This is our data today, minus a one, which A one. We can also just, oh, this is 4 4 9 27. We could do that. 07:02 Hmm. But I'd rather it be, I'd rather this be like a template that can restart next year. So I do want to do it like this. 07:11 I don't care about this number there. That's our number 35. And we just need to do this divided by today. 07:20 I'm sort of making this way too complicated by explaining it. If I just did it, it would be so much faster. 07:24 But this divided by 365. But 365 is not necessarily true. Cuz on leap years we have an extra day.

So I do want to set this here. 07:38 And then now we are going to get a percentage. There's the percentage. So that's our data . And now we wrap it with spark line. 07:48 Spark line. That's our data , our options. This is always difficult to figure out, but we have, we can copy paste it right from here. 07:59 If you don't know, we have a bar. We have our max is gonna be 100. No, it's not gonna be 100. 08:05 It's gonna be one, I think. And not a commas. Nope, that's not what I meant. We need a curly bracket and then a comma there. 08:18 I think the max is one. There we go.

That is a progress bar of how much is left. We can also get this same thing here. 08:29 We, whoops. What is, oh, we need that. All right. We have the percentage, 9.95 there. And now every day this will change. 08:40 I'm gonna center it. Center it. I might make it a little bit bigger too, so it's much easier to read. 08:46 Bam. I want to do something a little more nicer. I don't know if I want orange here, but we can always color change the color, I think. 08:58 Add a semicolon. I think it's like color one,  White.

I'm gonna say, let's see if, yep, that changes to white, but let's just change it to black for now. 09:11 Bam, we got black. Cool. But let's do that for the quarter as well, right? So this is 365. We have our start date and our end date . 09:24 Maybe we have settings on another page. So this is pro year progress and maybe we have like a settings page here. 09:33 I'm gonna format this. Go back to our mic sheet styles. You know what I wanna do? I don't want, I do want it dark style actually. 09:42 I want data style. I want, you'll see in a hot second. Yeah, this is cool.

So we have some settings we have start date . 09:56 I wanna move all of these over. Whoops. What I'm doing here is I'm just trying to get this in a column. 10:10 So it's end date there and I'm command xing it because I don't wanna mess any of this stuff up. So all of these are changing based on where I'm moving them because I'm cutting and pasting them. 10:28 And this is gonna be days in a year, in year days, let's just call it that. And we can also redo this styling if we want. 10:44 Make it look nicer. Here we go. Okay.

Now these are what we have to fill in the start date , end date of the year. 10:56 And now what I want to do is start date . Mm. Do we wanna figure out what quarter are we in now automatically? 11:06 Ooh, we can figure out what month it is. Okay. This actually, the month one is gonna be much more interesting than the quarter one. 11:13 I don't know what we're gonna do with the quarter, but let's, the, the month one's sort of automatic. Okay? It's gonna seem weird. 11:23 Let's do this.

Okay, there's today and that's the number of days in, but we know the end of the month. 11:36 Let's do a named range for, hmm. This is years. We don't need paste format . Hmm. Do we want That Here? 11:54 Max is going to, Hmm. Did that something change? Oh yeah, we did change something here. This information. Hmm. Okay, we have today. 12:10 We know today, let's do it over here today. Week have end of month. So this is EEO m, EEO m or e o m oh e o month, return to date on the last day of the month. 12:33 That falls on the specified number of days before or after today's date. Okay?

So we have today and we do months zero. 12:42 We're gonna change this back to format . Number , number , nope, sorry, format , number , date. So we can see this. So this is the last date. 12:52 Today is February 5th, and this gives us the last date of the month. We can also get, let's do this, e o m today, negative one. 13:06 And that's the last day of the last month.

So all we need to do is e o m I'm, I'm using this right? 13:14 Plus one gets us the first day of this month and then eom today, zero two is the last day of the month. 13:23 So we actually have to do, now we can see equals this minus this 27, which is wrong, right? There are 28 days in, in the month of in the month of February. 13:37 So actually we don't need this plus one there. So we know the end date and we know today's date or no, not today's date, the last date of the last month. 13:48 This is cool, right?

So we can take these and we know this is minus, this is gonna get us a number 28 format . 14:02 Number number there. That's 28, right? Whatever month that it is, it's in. It's gonna give us the number of dates. 14:08 This is the and then we go, ah, Jesus. End of month, go here. This is the denominator of our percentage. 14:22 Our numerator is going to be today minus the last day of the <laugh>. The last day of the last month, right? 14:35 So now this should be a percentage. No, cuz it's today's date. Let's wrap that. There we go. 17.86. Boom.

There we are 17% of the way through today, this month. 14:50 This is our Number that we have to put here right inside This, right? And it's still gonna be one. We can change this maybe color to gray. 15:13 I don't know. Let's put that there. Boom. 17 morning. Eight, six. So now if we were in, you know, every day it's gonna come back and it's gonna give us exactly how much is left in the month. 15:25 No, how much, sorry. The progress of the month, not the what's left in it. The what's left in it is one minus this. 15:32 Cool. Isn't this fun? <laugh>?

No, it's, it's, it's pretty nerve wracking. I don't know if we're gonna get through day. 15:39 I, i don't know. Quarter is gonna be weird, right? Because we need to, how do we figure out what quarter are we in? 15:47 How do we figure out what, what week we're in actually is end of month, we have weekday, are we just gonna do that like the week, the weekday of the week? 16:08 Or is it like based on like minutes or hours? I guess it's gonna only be one outta seven, right? Like let's, let's figure that out. 16:20 So we still need, it's not gonna be in a month. What do we need?

We got weekday And let's change this to just a number or No, not date format , just a number . 16:43 That's one because today's Sunday. Okay, so today's Sunday. So it's the number one and we are then one seventh of the way through. 16:52 So it's just gonna take the weekday if divide buy seven, right on the last day, we are seven outta seven. 17:00 Or should we do outta eight or should it be the number we're in the, well here we go. <laugh>, we're gonna do this. 17:09 This is easy, okay?

If, if you see any problems with this, let me know because it might be a little off, right? 17:18 It might be like, okay, we've, we're in the middle of the day. Now it might not be a sim. I've sort of simplified this by saying just pick the weekday and we're Sunday through as Sunday is one day. 17:31 Monday is gonna be <laugh> fifth, 30% all the way through. Two outta seven, right?

Two outta seven is gonna be 28% over the way through already once we're hit Monday, which is sorta wrong, we have now, but I'm not sure we can figure out, I mean we probably can get this more minute, right? 17:55 We have now, let's see, format Number . This is date, time, format , number , date Time's. It just gets us sort of everything we need. 18:11 We got the hour. Can we get hour? It is, that is the hour And Whoops. How many hours? So 17, that's 5:00 PM 17 of 24, right? 18:40 Of this day then plus No. Now we have weekday, but we want to do weekday minus one.

Hear me out. 19:01 We wanna do weekday minus one times 24 for every day that has passed, we're gonna multiply by 24 to get the number of hours. 19:12 Right? And then So today it's 17 hours into Sunday. So we're gonna take all of that and divide it, but, and then the denominator's gonna be seven days, times 24 hours, right? 19:29 So that gets us the progress of how far in the week. That's, if we wanna say that Sunday is the first day of the week. 19:36 We might not want to, we want, might want to say it's the last day of the week.

But for our intents right now, we are now 10% of the way through of the week. 19:46 Oh, this is cool because we are 17 hours out of 17 hours out of seven times 24. Yeah, 10%. Cool. 19:57 So now we have a progress for the date sorry, the week as well. And we just Get out of here, all of this, put that in there. 20:13 Our max is still gonna be one. Great. So we have week, month, okay? Now I've been dreading quarter. Do we want quarter? 20:22 Do you want to figure out quarter?

It's gonna be a bit of embarrassing if I can't figure out quarter, but let's say we figured out week, right? 20:30 We got a pretty minute, got 10, we're 10% of the way through cuz we're almost 6:00 PM on a, on a Friday. 20:38 Not a Friday. Oh my god. Today's Sunday. <laugh> not Friday at all. All right, how do we figure out quarter? 20:45 I mean we, okay, actually this is way easier than I thought. Okay? We know today because, and we know that the month is two, so we can maybe do like a switch . 21:01 This is gonna be weird if it's switch . Okay, so this, this is weird.

So we have an expression and then we're going to case one, case two, we're gonna say if it's between these two things, okay? 21:14 The case is this month, now it's two. And that is gonna be that it's oh, how do you do this? 21:35 Well we can do one, we can do q1. Two, one. I think we can actually, no, we don't need the quotes, right? 21:50 And 3, 1 4 is gonna be two. That's the month, the month, the fourth month wait, there's 12 months, three months in each quarter, right? 22:02 So five is two, six is two, seven is three, eight is three, nine is three, right?

10 and 11 are four. 22:17 And 12 is four, right? So now whichever month we're in, we know what quarter we in. Okay? So right now we are in quarter one, which means does that matter? 22:34 I don't know if this actually matters, but I did show you how to use switch in a weird way. So the fact that we're in q1, does it matter what quarter we're in? 22:46 Yes, because we, we have a beginning date and an end date . Mm.  I think I know why. Because if we have a start date and the end date of the years, we know what quarter one is. 23:04 Quarter one start, quarter two, one end, right?

We can do this programmatically. We can say this is this, this is equal to this. 23:25 Do we know? Hmm? I don't know if this quarter Hmm. My little brain fog here. What have we got here? 23:43 Hmm. I mean, could, did do months. Mm-hmm. <affirmative>. All right, this is a little embarrassing that I can't figure this out right away. 24:00 You're probably screaming at the video right now. I'm gonna pause the video for a hot second, okay? I really did only pause the video for a hot second. 24:07 I think we just know, we know this is the start.

We, we, we know the end of the quarter is gonna be March. 24:16 The end of March, right? Like that's, there's no other time. So we just need the year, right? We need, we could actually do date. 24:32 We have to con we can convert. We just need the year from this here. The month is going to be one day is gonna be one. 24:46 Yeah. And we know that this is gonna be March. There's always 31 days in March, right? Are there? Yes, there are <laugh>. 25:01 We know q1 q, q2, we know Q2 is going to be, this is sort of weirdly easy.

Like simple, maybe not easy, but simple. 25:11 We just gotta do this for each quarter, I think. Then we'll know, we'll know if when today is right. Number four April 1st. 25:25 And this is going to be, oops, We want, we want B one. Actually, we only want to really change that. 25:36 Did I get this wrong? <laugh>, B one, B one I, here we just change B one. Gotta hold that when we copy it B one and then hold that if we copy it. 25:49 This is wait, what is it? January, February, March, April, may, June 6th. Okay. It is that 31 June 30th.

And then we're gonna copy this. 26:08 This is so embarrassing that I'm getting the court. You're like, oh my god. The, we know the quarters everyone who works the quarter like all the time, like taxes, accounting this is just gonna be seven. 26:21 This is gonna be eight, but August has 31 days, right? Yeah. Quarters Are weird. Wow. But again, we might deal with the quarter ends differently. 26:39 I dunno. All right, so this is <laugh> not, wait, did I do this wrong? 9, 10, 11. This should be 10, right? 26:52 Seven.

No, this should not be eight, this should be nine, sorry, 9 30, 10. And this will be 1231. Perfect. Okay, we have the start and end date of each quarter so we know what quarter and we know what quarter we're in. 27:08 I don't know if engi this matters <laugh>. Yes, because we need to know. Ah, I, I got it. I think I'm figuring this out based on what quarter it's in. 27:21 We're in do we need to switch again?

I think we could have just done this with one switch instead of two expression in case if we're in one we want, if we're in quarter one, we want this start. 27:49 I think this is over complicating things. One, two or three. Start here. 2, 3, 4. Start here. I think this is way too complicated for what we want to do. 28:06 Two is here, three is here, four is here, right? That's it. Yeah. 1, 2, 3, 4. We have the start date Because every is every quarter a different number of days. 28:31 Is that why? Oh my god. Oh wait, let's just do it all here.

Let's just find the amount of day. 28:38 This is just gonna be the amount of days in the, yeah, so if it's q1 we want today minus the start date gonna be divided by the end date minus the start date . 29:07 Yeah, let's just figure it out right here. Boom. So now cuz we're in q q1, this is perfect.

Now for two, we do that same thing today cause we're not gonna get there, but we're gonna do the exact same thing today minus the start date divided by Q2 minus q1. 29:39 And this might be off by a little bit, but it is o okay, so we do today cause we can always fix that later if we have an issue divided by, what is it? 29:56 Q3 end minus Q3 start. Then we might have to add one somewhere here. Today minus q1 start divided by, no sorry, not q1, q4, Q4 minus the start. 30:21 There we go. Now change this format to a percentage. Oh, it's just pay special format only .

Boom, we got 39%. 30:33 Whew. That was a lot tough to do. I bet I overcomplicated it. But this spark line now it won't be all righteous. 30:44 All right. Same max is one. We just paste that thing right there. Again, we can just, maybe we don't have to have that in there. 30:52 Might just want to put it over here. Whew. That is cool. So now we have a year progress, we have a quarter progress, we have a month progress and a week progress. 31:01 Wow. We can extend these long as far as we want visually if we want to line it up differently.

We have a big percentage number right here. 31:11 Again, the quarter one might be off by a day or two. We'll find that out. If we look at this at the end of the quarter or the beginning of the quarter, we might see some weird weirdness there, but I showed you on the other one how to sort of fix that. 31:21 It's just add one or minus one based on the date. Or change.

Or you might wanna change these dates instead of the last day. 31:29 Maybe it's the first day of the month or the last day of the last month before you can change that. 31:35 Let's make this, you know what, I want to insert a row between them just so we have something between them, but we don't want it to be as big. 31:46 Let's resize these rows to half to begin with. Perfect. And what I wanna do is I wanna border them. Let's do a gray border. 31:58 Let's do thick just to see what it is first. Hmm. Maybe a little bit darker. Yeah, two shades. Sweet.

We're gonna do the same here, here and here. 32:12 Nice. Now this is a good like progress bar , right? We can even change this to like 20 big, big old numbers, right? 32:22 Let's make a little bit of a space here. There we go. Let's do the same over on this side. We're gonna go to left. 32:35 Let's insert little column, a tiny little padding there. Let's even these up. So they're the same amount of padding, recite selected columns, do 15 a little bit more, but oh, this is gorgeous, right? 32:49 What we could do is, is color these differently on the inside.

So instead of let's do, let's see what we can get away with like, yeah, that's cool. 33:02 And let's, instead of black here, we like, I like this gray. But maybe, let's see what orange looks like. L orange might look a little weird here. 33:14 Might be like your favorite color, right? Let's see, blue. Ah, you know, I like this gray. It all looks super sanitized. 33:24 <laugh>, I mean it's very understated, right? It's not in your face. No neon colors or anything. Nice thick borders.

We got our progress, we got the quarter, we got the month, we got the weak. 33:37 We might wanna switch up these locations like we might want, well we might want year at the top. We might want then week, then month and quarter up to you, right? 33:50 You can move these around. And nothing changes, right? We can move it like this. Or we could also do is decrease the size of these a little bit so that the year really stands out. 34:06 Or we can maybe not that small or we can increase this size 24.

We can also decrease the color a little bit more. 34:19 Can have them here, but they're a little muted, right? So now we see the year boldly. We started with just this <laugh> and now we're here. 34:29 We got our progress bar . We got our percentage left in the year. This is a pretty cool screenshot, audible kind of thing. 34:38 We might want to name this also year progress maybe year left. I liked it just year. Give it a little bit less padding over here, but this is pretty nice. 34:55 And screenshot audible, right? Give it little bit more room maybe or less room.

Maybe one less row up here. Maybe make this one the, all the same so they're even what was it? 35:13 I think it was 25. There we go. Yeah, looks pretty cool, right? I don't know what else to do here. 35:21 I think we've finished what we needed to do. We finished our display of progress bar percentage of the year left. 35:26 Da, we figured out the day's gone by. We figured out all of the percentages.

<laugh>, we got a little weird with the quarter here. 35:33 I think I over-engineered this, but at least for the year we got, if you wanna replicate this, you are more than welcome to. 35:40 It's pretty simple to get today. Get the start day of the year, the end date of the year, get that difference and then make the percentage right here. 35:47 And also you could, instead of having this today and here, you could just go to here and it's the exact same thing, right? 35:56 And now we only have one formula to deal with. Easy peasy.

We could have done that on all of these. 36:03 Kept that percentage right there. We can also change the font. Maybe do something like not that, oh my god. A funny like game one, but is canlas, eh, it's not that great. 36:20 I thought it would be a little bit nicer looking. It's not. But we have some other options. What was what we had? 36:26 Carla? Where 's Carla? Let's get Carla back or dancing script. I don't know if you really wanna do that. Anything but Ariel, right? 36:34 <laugh>. Jk. L m. Where 's Carla? Let's get Carla back. There we go. Nice. Carla. I love Carla font.

The numbers just look a little weird. 36:45 That's fine. Definitely something else styled differently than Ariel. Thanks for watching this whole video. I hope it wasn't too boring. 36:53 I hope you got something out of it. We built a clear a clone of your progress. Now we can check this every day and see our progress through the year. 37:03 Keep us motivated. Watch. We got some other videos. If you are on Better sheets.co. Watch the video. Send an email from sheets to your email. 37:16 Like some data .

There's a really cool video and you could probably use this as that data to like send this number . 37:22 9.59% of the year is done to your inbox every single day so you can get a notification of that. So go watch that video on better sheets.co if you're there, or yeah, if, if you're watching this anywhere, <laugh>, go to Better sheets.co. 37:37 There's a Send Data , a one Cell of Data to your inbox. There's a video about that. Go check it out.</div>