If you have a job that pays you differently depending on the day of the week, you might want to use the WEEKDAY function in Excel to calculate your earnings. The WEEKDAY function returns a number from 1 to 7 that corresponds to the day of the week for a given date. For example, WEEKDAY(“2/16/2024”) returns 6, which means Friday.
You can use the WEEKDAY function along with other functions such as IF, SUM, and SUMPRODUCT to calculate your total pay based on your hourly rates and hours worked for each day of the week.
The WEEKDAY function has the following syntax:
=WEEKDAY(serial_number, [return_type])
serial_numberis the date for which you want to find the day of the week. It can be a cell reference, a date value, or a formula that returns a date.return_typeis an optional argument that specifies how the days of the week are numbered. The default value is 1, which means Sunday is 1 and Saturday is 7. You can choose other values from 1 to 17 to change the order and range of the numbers. For example, if you use 2, then Monday is 1 and Sunday is 7.
The WEEKDAY function returns an error if the serial_number is not a valid date or if the return_type is not a valid number.
Procedures
To use the WEEKDAY function to calculate with different hourly pay rates, you need to follow these steps:
- Enter your hourly rates for each day of the week in a range of cells, such as B2:B8. You can use any order or return type that suits your preference, as long as you are consistent throughout the calculation.
- Enter your hours worked for each day of the week in another range of cells, such as C2:C8. Make sure the order of the days matches the order of the rates.
- Enter the dates for which you want to calculate your pay in another range of cells, such as D2:D8. You can use any date format that Excel recognizes, such as 2/16/2024 or 16-Feb-24.
- In the cell where you want to display your total pay, enter the following formula:
=SUMPRODUCT(B2:B8,IF(WEEKDAY(D2:D8,2)=ROW(1:7),C2:C8,0))
- The SUMPRODUCT function multiplies the corresponding values in two or more arrays and returns the sum of the products. In this case, it multiplies the hourly rates by the hours worked for each day of the week and adds them up.
- The IF function returns a value based on a logical test. In this case, it tests if the WEEKDAY function returns the same number as the ROW function for each date. The ROW function returns the row number of a cell or a range of cells. In this case, it returns the numbers from 1 to 7, which match the return type 2 of the WEEKDAY function. If the test is true, the IF function returns the hours worked for that day. If the test is false, the IF function returns 0.
- The WEEKDAY function returns the number of the day of the week for each date, using the return type 2, which means Monday is 1 and Sunday is 7.
- The formula works by multiplying the hourly rate for each day of the week by the hours worked for that day, if the date matches the day of the week. Otherwise, it multiplies by 0. Then, it sums up the products to get the total pay.
Explanation
To understand how the formula works, let’s look at an example with some sample data. Suppose you have the following hourly rates and hours worked for each day of the week:
| Day | Rate | Hours |
|---|---|---|
| Monday | $15 | 8 |
| Tuesday | $18 | 6 |
| Wednesday | $20 | 7 |
| Thursday | $22 | 5 |
| Friday | $25 | 4 |
| Saturday | $30 | 3 |
| Sunday | $35 | 2 |
And suppose you want to calculate your pay for the following dates:
| Date |
|---|
| 2/16/2024 |
| 2/17/2024 |
| 2/18/2024 |
| 2/19/2024 |
| 2/20/2024 |
| 2/21/2024 |
| 2/22/2024 |
To calculate your total pay, you enter the following formula in the cell where you want to display the result:
=SUMPRODUCT(B2:B8,IF(WEEKDAY(D2:D8,2)=ROW(1:7),C2:C8,0))
The formula performs the following calculations:
- The WEEKDAY function returns the number of the day of the week for each date, using the return type 2, which means Monday is 1 and Sunday is 7. The results are:
| Date | WEEKDAY |
|---|---|
| 2/16/2024 | 6 |
| 2/17/2024 | 7 |
| 2/18/2024 | 1 |
| 2/19/2024 | 2 |
| 2/20/2024 | 3 |
| 2/21/2024 | 4 |
| 2/22/2024 | 5 |
- The ROW function returns the row number of a cell or a range of cells. In this case, it returns the numbers from 1 to 7, which match the return type 2 of the WEEKDAY function. The results are:
| ROW |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
- The IF function returns a value based on a logical test. In this case, it tests if the WEEKDAY function returns the same number as the ROW function for each date. If the test is true, the IF function returns the hours worked for that day. If the test is false, the IF function returns 0. The results are:
| IF |
|---|
| 0 |
| 2 |
| 8 |
| 6 |
| 7 |
| 5 |
| 4 |
- The SUMPRODUCT function multiplies the corresponding values in two or more arrays and returns the sum of the products. In this case, it multiplies the hourly rates by the hours worked for each day of the week and adds them up. The results are:
| SUMPRODUCT |
|---|
| $0 |
| $70 |
| $120 |
| $108 |
| $140 |
| $110 |
| $100 |
- The final result is the sum of the products, which is $648.
Therefore, your total pay for the dates in the range D2:D8 is $648.
Scenario
To illustrate how the formula works in a real scenario, let’s imagine that you are a freelance graphic designer who works for different clients on different days of the week. You charge different hourly rates depending on the day of the week and the complexity of the project. You use the following table to keep track of your rates and hours:
| Day | Rate | Hours |
|---|---|---|
| Monday | $25 | 4 |
| Tuesday | $30 | 5 |
| Wednesday | $35 | 6 |
| Thursday | $40 | 7 |
| Friday | $45 | 8 |
| Saturday | $50 | 9 |
| Sunday | $55 | 10 |
You want to calculate your pay for the week of February 16, 2024 to February 22, 2024. You use the following dates in the range D2:D8:
| Date |
|---|
| 2/16/2024 |
| 2/17/2024 |
| 2/18/2024 |
| 2/19/2024 |
| 2/20/2024 |
| 2/21/2024 |
| 2/22/2024 |
You enter the following formula in the cell where you want to display your total pay:
=SUMPRODUCT(B2:B8,IF(WEEKDAY(D2:D8,2)=ROW(1:7),C2:C8,0))
The formula performs the same calculations as before, but with different values. The results are:
| SUMPRODUCT |
|---|
| $450 |
| $550 |
| $100 |
| $150 |
| $210 |
| $280 |
| $360 |
The final result is the sum of the products, which is $2,100.
Therefore, your total pay for the week of February 16, 2024 to February 22, 2024 is $2,100.
Other Approaches
There are other ways to use the WEEKDAY function to calculate with different hourly pay rates in Excel. Here are some alternative formulas that you can try:
- You can use the CHOOSE function instead of the IF function to return the hours worked for each day of the week. The CHOOSE function returns a value from a list of values based on an index number. In this case, the index number is the WEEKDAY function. The formula is:
=SUMPRODUCT(B2:B8,CHOOSE(WEEKDAY(D2:D8,2),C2:C8))
- You can use the VLOOKUP function instead of the IF function to look up the hours worked for each day of the week from a table. The VLOOKUP function returns a value from a table based on a lookup value and a column index number. In this case, the lookup value is the WEEKDAY function and the column index number is 2. The formula is:
=SUMPRODUCT(B2:B8,VLOOKUP(WEEKDAY(D2:D8,2),A2:C8,2,FALSE))
- You can use the SUMIFS function instead of the SUMPRODUCT function to sum the products of the hourly rates and the hours worked for each day of the week. The SUMIFS function returns the sum of values that meet multiple criteria. In this case, the criteria are the WEEKDAY function and the ROW function. The formula is:
=SUMIFS(B2:B8*C2:C8,WEEKDAY(D2:D8,2),ROW(1:7))
These formulas will give you the same result as the original formula, but they use different functions and arguments. You can choose the one that you prefer or that suits your data better.
