Formula: sum weekend values to show only week date in an excel report

I have this table with some dates that fall in the weekend (Saturday and Sunday):

formula excel

I want to calculate how many incidents I have but for my final results, I don’t want to show those weekends. I want to sum values of the weekend into the next week day, in this case, on Monday. If you are looking for the Power BI version, read Power BI: sum weekend values to show only week date.

formula excel


When I use the formula ?

To calculate all values without showing the weekend.


How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".


How are the formula ?










First thing it is to create a pivottable, it is much easier (later I will show how to do it without it):

formula excel

I will create a calendar without weekend by putting in the cell K3, this simple formula making reference to the first day of my column “date”:


In the cell K4, I will put this formula:


And just copy it to below cells.

formula excel

In the cell L3, I will make reference to the result of P1 in the pivotable, in fact, I just want excel to give me the formula, which is:


I will change the last part “DATE(2023,1,12)” by “$K3”:


And with that, I will create my final formula so in my cell L3, it will be:


I will copy this formula to the rows below and to the columns beside without forgetting to change P1 by P2, P3, P4 and P5. For the last column “Grand total”, I can use the same process:


NOTE: the formula is shorter because it doesn’t include the “priority” (P1, P2, etc.)

formula excel

Or just do a SUM formula like that for the cell Q3:


Now if I don’t want to use a pivot table, I can get the same result with one table. For that, I will create the calendar without weekend by putting in the cell A2 the first date which is the 12/01/2023 and in the cell A3, the same formula as the one above:


NOTE: don’t forget to change the cell reference

From this point, the formula will be different. So in the cell B2, put this formula:


Copy it rows below and also in the next columns, just don’t forget to change P1 by P2, P3, etc. And for the last column, I just do a SUM.

formula excel

Interesting Management