How to Calculate Weekly Hours from Monday to Sunday in Excel
Question details
Calculate and display a weekly total of hours specifically on Sundays, while leaving the cells for other weekdays blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking daily working hours and summarizing them weekly on the last day of the standard work week (Sunday).
- Observed behavior
- Requires a dynamic formula that checks if the current row's date is a Sunday and sums the hours from the preceding six days along with the current day, returning empty for non-Sundays.
Ensure your dataset contains a column for Dates formatted correctly as date values, and that your data is sorted chronologically if using positional references.
Use IF, WEEKDAY, and SUMIFS to Calculate Weekly Totals
This is the most robust method. It conditionally sums the hours from the previous six days and the current date based on actual date values, ensuring accuracy even if some daily records are missing.
The SUMIFS function adds values based on multiple conditions. By combining it with the IF and WEEKDAY functions, we can instruct the spreadsheet to only perform the calculation when the date is a Sunday, effectively grouping Monday through Sunday.
Assume Column A contains your dates and Column C contains your daily logged hours.
Select the cell where you want the first weekly total to appear (e.g., D2) and type: =IF(WEEKDAY(A2,2)=7,SUMIFS(C:C,A:A,">="&A2-6,A:A,"<="&A2),"")
Press Enter, then click and drag the fill handle (the small square at the bottom right of the cell) down to apply this formula to all rows in your timesheet.
Use OFFSET for Strict Sequential Data
If your spreadsheet has exactly one row for every single day without any gaps, you can use the OFFSET function to dynamically select the preceding seven rows to sum.
Easily Track Timesheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical and logical functions like SUMIFS, OFFSET, and WEEKDAY. Manage your timesheets, calculate weekly totals accurately, and track payroll with ease.
- 1. Open your timesheet: Launch WPS Office and open your daily hours tracker spreadsheet.
- 2. Insert the weekly formula: Select the weekly total column and paste the SUMIFS calculation formula.
- 3. Drag to calculate: Drag the bottom-right corner of the cell to automatically calculate totals for all matching Sundays.
Frequently Asked Questions
Why does my weekly total formula return an error in the first few rows?
If you are using the OFFSET function, it attempts to look backwards by 6 rows. If your current row is too close to the top of the spreadsheet (e.g., row 3), there are not enough rows above it to fulfill the formula, resulting in a #REF! error. Wrap your formula in an IFERROR function to leave those cells blank.
How can I change the weekly total to display on Friday instead of Sunday?
You can modify the condition inside the WEEKDAY function. The formula uses WEEKDAY(A2,2)=7 to target Sunday. If you want Friday to be the trigger day, change the '7' to a '5' (e.g., WEEKDAY(A2,2)=5), since Monday is 1 and Friday is 5.
What happens if I have multiple time entries for the same date?
If you use the SUMIFS method, the formula looks at the actual date values in your column and accurately sums all hour entries that fall within the specified date range, regardless of how many rows there are. However, the OFFSET method will fail in this scenario, as it strictly counts physical rows.
