00:00Here's a challenge for you. My local hockey team has asked me for my help in telling up their total
00:05of all the payment received from their player. However, the catch here is that there's a separate
00:10rate for weekdays and weekend. If you have similar tasks, these Excel tips and tricks will help you.
00:17To start off with, let's identify this function called weekday. It takes one argument, which is
00:24going to be a date. Let's assign this date here and hit enter. You can see weekdays actually returns
00:30integer number, a whole number ranging from 1 to 7. 1 being Sunday, 2 being Monday, 3 being Tuesday
00:39and so forth until Saturday, which is 7. And after that, Sunday is 1 back again. Now you can use
00:47this
00:47same weekday function against your all function and the if function to determine whether it's going to
00:54be this payment or that payment. Let me demonstrate. Basically, we're going to say all function and
00:59inside there, let's put an equal here, all function and we're going to inside the all function, there's
01:04going to be two conditions, which is going to be for Saturday, let's say weekday and the date over
01:10here on C9. And if this is number one, which is going to be Sunday and the second argument on
01:19the all,
01:20let's put weekday again, and then C9 again, which is the date, this is going to be 7. So 1
01:28means Sunday,
01:307 means Saturday. If you hit enter, you can see this is actually Tuesday, I think. That's why it's
01:35shown as false. But this one here, 12 May is Sunday, it's showing as true. So this is a condition
01:41for our
01:41is statement here. So basically, if we encapsulate the all function or use the all functions condition
01:48output as a condition for the if statement, now you can say that if this is true, which means if
01:54it's
01:54a weekend, we're going to charge this rate at 1185. Make sure you press F4 to make both row and
02:00column
02:01fixed, because we can apply the same form the rest of row later on. And if it's not true, we're
02:06going
02:06to charge them at the weekday rate at 875. Make sure press F4 again, so that your column and rows
02:12are
02:12fixed. Close parentheses and hit enter. Now you can see that you do get a payment rate here.
02:18And now apply the same formula to the rest of row like this. And then you're done.