Skip to playerSkip to main content
Learn how to calculate total base on weekday and weekend rates in Excel.

To calculate payments based on weekdays and weekends in Excel, you can use functions like WEEKDAY() to determine the day of the week. For weekdays, which are Monday through Friday, you can use the IF() function to assign one rate, and for weekends, Saturday and Sunday, another rate can be assigned. To calculate working days including weekends in Excel, you can utilize the NETWORKDAYS() function, which automatically excludes weekends from the count. By combining these functions and logical tests, you can easily determine payments based on different rates for weekdays and weekends. Simply input the rates and date ranges, and Excel will do the rest, providing you with the total based on weekdays and weekends rates.

At the end of the video, you should be able to answer the following questions.
Calculate based on weekdays and weekends
How do I calculate weekday and weekend in Excel?
How do I calculate working days in Excel including weekends?
Calculating a price based on a weekday and date range
Calculate total based on weekdays and weekends rates in Excel


Here's the formula featured in my video.
Let's break this formula down.

=IF(OR(WEEKDAY(C9)=1,WEEKDAY(C9)=7),$D$6,$D$5)

IF: This is the function used to perform a logical test and return one value if the condition is true and another value if the condition is false.

OR: This function checks if any of the specified conditions are true and returns TRUE if at least one condition is met, otherwise FALSE.

WEEKDAY(C9): This function returns the day of the week corresponding to a given date. In this case, it's checking the day of the week for the date in cell C9.

$D$6 and $D$5: These are the values that will be returned if the condition in the IF function is true or false, respectively. $D$6 seems to be a reference to a cell containing a value for weekends, and $D$5 for weekdays.

So, in simple terms, this formula checks if the day in cell C9 is either Saturday or Sunday. If it is, it returns the value in cell D6 (presumably a weekend value). If not, it returns the value in cell D5 (presumably a weekday value).

Calculate based on weekdays and weekends,How do I calculate weekday and weekend in Excel?,How do I calculate working days in Excel including weekends?,Calculating a price based on a weekday and date range,Calculate total based on weekdays and weekends rates in Excel,

Check out my complete suite of Microsoft Excel Tips and Tricks.
https://www.youtube.com/@jjnet247/shorts
https://www.tiktok.com/@exceltips247
https://www.instagram.com/exceltips247/
https://www.dailymotion.com/ExcelTips247
https://www.pinterest.com/ExcelTips247/excel-tips-and-tricks/
https://x.com/ExcelTips247/media
https://www.reddit.com/r/Excel247/
https://www.facebook.com/XyberneticsInc/reels/

#microsoft #excel #tips #tipsandtricks #microsoftexcel #accounting #fyp #fypシ #exceltips #exceltricks

Category

📚
Learning
Transcript
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.

Recommended