Automatic Weekly Calendar View

0:00 We have a list of events and we want to create a calendar view. We want to create a weekly calendar view, maybe this week, next week, the week after, or a few weeks. We want to see these in a calendar. So we have a blank page we're starting with and let's create a calendar. This is going to be like, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. But we need the actual dates. So let's do this. Let's create this as the header . And then get today. So today is Friday. It would go here. Today. But, we want to get the dates of this whole week. We need to figure out a formula that will give us a date of whatever this week is. This today is going to change, right? This date is going to turn into Saturday, right? Richard Asks: Copy Template Tab and Rename to Date Every Week Organize Your Data! - Build a Kanban Board / Trello Board Make a Simple Calendar Template

0:47 Tomorrow. So this will need to be over here. So what ends up happening is we have equals weekday around today. That can give us a number . This is six. That means it's the sixth day of the week. If we did that yesterday, it would be five. If we did that the day before, it would be four. So what we could do is say this is equal to today, this is a Thursday. For example, today is Friday, so we can take the weekday of today, and we know we're going to get six here, but we only need one. So we do plus five. So we need today, and that gets yesterday. Now tomorrow will be Saturday, but this weekday will be one more as well. So today minus weekday today plus six is going to be Friday. Week Progress Bar

1:36 And plus seven is going to be Saturday. Take plus five, do plus four, plus three, plus two, plus And plus one. So as today increases, this weekday as well will increase one. So it'll minus it out. So every day of the week will be here. So let's include that. Let's make this a little nicer to see. And we may need only this row. Let's delete everything else. Let's even view this without any grid lines. And let's add a border around each of these. We may be able to make this a bit bigger. Because we want to actually show these events here. We have the dates here and the event name in B. Big Year Updated with PILLAR Template Mini Calendar

2:21 So we create a filter . Filter . The range is going to be the B column where A is equal to Whatever this date is, and now we're going to get an N A, but let's copy this across have the 22nd. Oh, and we need to make sure we put a dollar sign in front of the B dollar sign in front of the A's to lock them down there. And we have one event. So we want to show nothing else. So we say if an A comma nothing and in parentheses. So now we have a weekly team meeting here. Let's make this a little bit bigger. There we go. Let's see, if we have two events on the same day, we would show them in separate cells . But let's join them together. How To Filter Dates (They Are Numbers Too!) Filter a Database in Google Sheets based on Dates, Checkboxes, and Dropdown selections! How To Filter by Date in Google Sheets

3:08 We'll join with the delimiter , two quotes, but inside the two quotes, we'll put a command enter. So now all of the events are in the same cell. Let's center this as well vertically. Make this a little nicer to see. Let's use a different font. We can even color this to some degree. Or even do conditional formatting . Let's make a really cool thing here. Do some conditional formatting . Where we say, if date is today, we color it. Black with white text. That's Friday. Done. And now as the date moves, we'll see what today is and what events we have. Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell Google Sheets Interface Changes How to Highlight Today Only in Conditional Formatting

3:53 If we have all these events on the 21st, let's change those to 21st, go back and automatically we have our events here. We could make this wider to accommodate wider text. Even more. There we go. So we see all of our events on this day. There's how to make a weekly calendar view that's automatic. Anytime we add events or change the event title here, it shows up on our weekly calendar. Pretty cool, right? And this little fun formula makes sure that whatever week we're on, This week is shown here, Sunday to Saturday. If you're looking to get more out of your Google Sheets, make cool automations, do amazing things, subscribe today to BetterSheets right here on YouTube. Automatic Weekly Backup of Google Sheets Google Sheets Interface Changes Automatic Weekly Backup of Google Sheets How To Create Google Calendar Events in Google Sheets