Forum Discussion
Help with changing a formula
Hi - I am using a formula to sum hours worked for a week. Currently it calculating the values beginning on Sunday to Saturday. I would like to change it to sum from Monday to Sunday and display the result in column G on the Sunday of that week. I'm hoping you can help me. TIA
I am currently using the following formula.
=IF(WEEKDAY(A2)=7, IF(SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2)>0, SUMIFS(C:C,A:A, ">="&A2-6,A:A, "<="&A2), ""), "")
| A | B | C | D | E | F | G | H | |
| 1 | Date | Hours | Purchases | Rate | Daily Cost | Hrs / wk | ||
| 2 | 1-Jan | Wed | 2.5 | $20.00 | $50.00 | |||
| 3 | 2-Jan | Thu | ||||||
| 4 | 3-Jan | Fri | $20.00 | |||||
| 5 | 4-Jan | Sat | 3 | $20.00 | $60.00 | 5.50 | ||
| 6 | 5-Jan | Sun | $20.00 | |||||
| 7 | 6-Jan | Mon | $20.00 | |||||
| 8 | 7-Jan | Tue | $20.00 | |||||
| 9 | 8-Jan | Wed | $20.00 | |||||
| 10 | 9-Jan | Thu | $20.00 | |||||
| 11 | 10-Jan | Fri | $20.00 | |||||
| 12 | 11-Jan | Sat | 3 | $20.00 | $60.00 | 3.00 | ||
| 13 | 12-Jan | Sun | 3 | $20.00 | $60.00 |
1 Reply
I’ve answered more fully on your other post:
Need assistance to correct a formula | Microsoft Community Hub
But in relation to the specific point you mention in this one that “Currently it calculating the values beginning on Sunday to Saturday. I would like to change it to sum from Monday to Sunday”
I think this would be because you haven’t used the optional second parameter of the Weekday function that defines which day of the week is 1 and which is 7.
https://support.microsoft.com/en-us/office/weekday-function-60e44483-2ed1-439f-8bd0-e404c190949a
