Hey there stranger!

Sign up to get access.

Alejandro Asks: How do I Sum up Study hours from a Calendar Format?

About this Tutorial

A member, Alejandro, asks about taking their custom calendar inputs and figuring out study totals for a week. They created a beautiful input and just need to the calculations. I show how to use the data to do the calculations. If you've never used the FLATTEN() formula, this is a good example! And we use double UNIQUE().

Video Transcript

00:00 Hello. So Alejandro has this question about moving or calculating the study hours from this kind of calendar looking thing. What's interesting about this is that we've created, Alejandro's created a really nice input place.
00:19 It has dropdowns, it has start and finish times. It has days of the week, but unfortunately we need to like add up this time.
00:26 We want to have some dashboard for it. So what I would do is immediately I would not change anything about this input, right?
00:35 The input works great. We have a start finish time very clearly marked over here times of the day that are delineated for certain activities.
00:44 And then the person trying to calculate how much time is being used for studying is gonna very easily click on one of these buttons and insert study grammar or something.
00:55 And we have this great chart here showing it. We just want to calculate how many hours are actually being spent studying.
01:04 Okay? So first off, I, some other people might have some idea of like doing some fancy calculations of, of finding the names in here and then finding the start and finish time doing that.
01:21 I think actually what we could do is just re reapply this data so we know that there, it's six segments here.
01:33 We know each of these segments, how much time they are. So we don't need to, we can do the calculation ourselves.
01:38 And we just have five, seven days, but Saturday and Sunday look like they're empty, but it's fine. Seven days that are all the same.
01:45 So what we really need is activity hours and day. That's really the only things here. And so what we can do is we can say this is equal to this and that is Monday.
02:10 We're gonna do, it's looks like it's 60. Well, we can do 60 minutes cause there's like a half hour here.
02:17 We, we can do this a couple ways. We can do 60 minutes and then divide by 60 at the end.
02:21 Or we can say this is one hour, one hour point, five hours. This is three hour session. This is a six hour session and this is three hours.
02:34 So we can have those for Monday. Again, we can do this any number of ways we can. Now that's Monday.
02:45 Now this is just using the equal sign to say E eight. If we drag this down, now we have all of Monday.
02:56 There we go. Now we just do the same for Tuesday. What is that? Six times? That's five. And we use the same amount of hours.
03:09 And instead of this equals E 13, it's gonna be F eight. All right here F eight. Okay. And now we drag that down and you see now we have study grammar one hour Tuesday.
03:30 We do the same wins this day. I don't know why that's difficult. We have the same block. We can actually copy paste this Thursday, Friday, Saturday, Sunday.
03:45 That is all of our days. Friday Two Here. Oh, I think I did something wrong. The first one is, oh, there we go.
04:10 Saturday, Sunday. And we only have to set this up once, right? We can delete all of these rows. We can delete all of these columns.
04:29 There we go. Now we just need, what's Wednesday? G eight. G eight down to here, then H eight and put down to Thursday.
04:56 Again, this looks really boring, right? H i eight, but oops, we screwed something up. Oh, we have two equal signs.
05:07 Looks really boring, but it's gonna get us our next step. H i Jake, is that J eight? H i j.
05:19 And then the last one is gonna be K eight right there. And now anytime that we change this, right, we add study grammar here, 19 to 22.
05:37 It is right here for three hours. Now all we have to do for each one we can do like unique E eight, K 13.
05:56 Oh, I think there's something here. Oh, we want to flatten it. There we go. So now we flatten all that we can sort it to, so it's all in like alphabetical order.
06:16 What is this sort? I always forget sort, sort column. I'll if these, this sort column is one. Sending drew, we want it we have some issue.
06:46 It looks like, oh, we probably have to do unique again because of the flatten. Yeah. Now we have all the unique stuff.
06:55 So we do filter for listen to audio filter. Arrange is going to be these hours maybe sorry. Semicolon condition is gonna be that the activity is equal to here.
07:19 Now we're gonna get a filter of all of those, but we really want is some. And now we can do a sum if here or something.
07:25 But I like doing some filter for some reason. Now the only thing we have to change is boom, boom, boom.
07:33 Now we have all of the things that in here we have all of them added individually together and the sum of the amount of study hours.
07:43 So we study grammar, six hours, we've studied it three hours here. Is this correct? Three half hour, one hour, one hour.
07:51 So one plus one plus half an hour. Three should be three and a half. Hmm. Unless the study grammar is not, is it three and a half?
08:09 Half hour Three, six and a half. Let's see what happened. Study grammar. Study grammar. Oh, maybe this is just rounded.
08:28 Nope. See what does this filter do? I don't know why this is so weird. That's one. What is that six?
08:46 Why is that six? I'm looking right at it, right? Oh, maybe <laugh>. I know why it's this 0.5 because this 0.5 is supposed to be comma five.
08:57 I. We are in a different locale. This is Spain. There we go. That's why I am doing dots. But these are supposed to be commas.
09:10 That's why it's not adding the 0.5 Mine. Bad, bad, bad. My bad, Alejandro. All right, I hope this helps. And <laugh>, sorry too much.
09:22 But that's what I did. That's how I figured out. And then you can the, the weekly study hours, there we go.
09:27 6.5, six and a half. Again, you could do this a number of ways. Maybe by not having hours here but having the start and finish so that you can change that.
09:42 Maybe you want to change that a lot these different times. Maybe you want to add more to this. So that's, this isn't very robust to do that.
09:51 But your question was just to how do I add the weekly study hours? Again, then you can maybe pick out of all of these, this is study grammar, six and a half, but maybe you want to pick which one of these is actually studying and review flashcards.
10:05 Might be flash charts, flashcards probably could be included in study or you might want to separate it. There you go.
10:14 Hopefully this is helpful. Bye.