'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate a Monday-to-Friday Running Total in Excel
Question details
The user needs a formula to calculate a running total that resets on Mondays, excludes Saturday and Sunday, and avoids carrying values over from the previous week.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking weekly expenses or values where weekends are excluded and calculations must cleanly restart at the beginning of each new workweek.
- Observed behavior
- Previous attempts using only the OR function resulted in logical TRUE/FALSE values, or produced random numbers when copied down the column.
Ensure your dataset contains a column for dates, a column with the daily values you wish to sum, and optionally a column displaying the day of the week as text.
Use a Nested IF Formula for Weekly Running Totals
Combine the IF, OR, and WEEKDAY functions to evaluate the day of the week, skip weekends, and reset the running total every Monday.
This formula relies on the WEEKDAY function to determine if the date is a Monday (using return type 2 makes Monday = 1). If it is Monday, the total resets to the current day's value. For weekends, it returns a blank. For Tuesday through Friday, it adds the current day's value to the previous day's running total.
Ensure Column A contains your Dates, Column D contains the daily values or expenses, Column F contains the text day of the week (e.g., 'Monday'), and Column G will hold your running total.
Select the first cell in your running total column (e.g., G3). Type the formula: =IF(OR(F3="Saturday",F3="Sunday"),"",IF(WEEKDAY(A3,2)=1,D3,D3+G2)) and press Enter.
Click the fill handle (the small square at the bottom-right of cell G3) and drag it down to apply the formula to the rest of your data set. Ensure the cell references adjust appropriately for each row.
Easily Build and Manage Complex Formulas in WPS Spreadsheet
WPS Spreadsheet provides robust support for logical, date, and mathematical functions, allowing you to easily build and manage weekly running totals without errors. It is highly compatible with complex data workflows.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the weekly data.
- 2. Insert the logical formula: Click on the target cell for the running total and enter the formula: =IF(OR(F3="Saturday",F3="Sunday"),"",IF(WEEKDAY(A3,2)=1,D3,D3+G2)).
- 3. Drag to fill: Use the fill handle in the lower right corner of the cell to drag the formula down the entire column.
Frequently Asked Questions
Why is my formula returning TRUE or FALSE instead of a number?
If you only use the OR function without nesting it inside an IF function, Excel will evaluate the condition (e.g., is it Saturday or Sunday?) and output a logical value of TRUE or FALSE. Wrapping it in an IF function allows you to specify numerical outputs or a blank output ("") instead.
How does the WEEKDAY function know when it is Monday?
By using the formula WEEKDAY(A3, 2), the second argument '2' tells the function to treat Monday as the 1st day of the week and Sunday as the 7th. Therefore, if the function equals 1, the formula knows it is Monday and safely restarts the weekly total.
Can I completely hide the weekends instead of showing blanks?
The formula =IF(OR(F3="Saturday",F3="Sunday"),""...) returns an empty string for weekends, making the cells look blank. If you want to completely hide those rows from view, you would need to use Excel's Data Filter feature to uncheck Saturday and Sunday from your date or weekday column.
