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

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.

 

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 ?

=IF()

=OR()

=TEXT()

=IFNA()

=VLOOKUP()

=IFERROR()

=GETPIVOTDATA()

=SUM()

=COUNTIFS()

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

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”:

=B3

In the cell K4, I will put this formula:

=K3+IF(OR(TEXT(K3+1,"ddd")="sat",TEXT(K3+1,"ddd")="sun"),3,1)

And just copy it to below cells.

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:

GETPIVOTDATA("Incidents",$B$1,"Priority","P1","Date",DATE(2023,1,12))

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

GETPIVOTDATA("Incidents",$B$1,"Priority","P1","Date",$K3)

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

=IF(IFNA(VLOOKUP($K3,$B:$B,1,0),0),IF(TEXT($K3,"ddd")="mon",IFERROR(GETPIVOTDATA("Incidents",$B$1,
"Priority","P1","Date",$K3),0)+IFERROR(GETPIVOTDATA("Incidents",$B$1,"Priority","P1","Date",$K3-2),
0)+IFERROR(GETPIVOTDATA("Incidents",$B$1,"Priority","P1","Date",$K3-1),0),IFERROR(GETPIVOTDATA
("Incidents",$B$1,"Priority","P1","Date",$K3),0)),0)

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:

=IF(IFNA(VLOOKUP($K3,$B:$B,1,0),0),IF(TEXT($K3,"ddd")="mon",IFERROR(GETPIVOTDATA("Incidents",$B$1,
"Date",$K3),0)+IFERROR(GETPIVOTDATA("Incidents",$B$1,"Date",$K3-2),0)+IFERROR(GETPIVOTDATA
("Incidents",$B$1,"Date",$K3-1),0),IFERROR(GETPIVOTDATA("Incidents",$B$1,"Date",$K3),0)),0)

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

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

=SUM(L3:P3)

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:

=A2+IF(OR(TEXT(A2+1,"ddd")="sat",TEXT(A2+1,"ddd")="sun"),3,1)

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:

=IF(IFNA(VLOOKUP($A2,Sheet1!$C:$C,1,0),0),IF(TEXT($A2,"ddd")="mon",SUM(COUNTIFS(Sheet1!$B:$B,"P1",
Sheet1!$C:$C,$A2))+SUM(COUNTIFS(Sheet1!$B:$B,"P1",Sheet1!$C:$C,$A2-2))+SUM(COUNTIFS(Sheet1!$B:$B,
"P1",Sheet1!$C:$C,$A2-1)),SUM(COUNTIFS(Sheet1!$B:$B,"P1",Sheet1!$C:$C,$A2))),0)

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.