With PivotTable: Count consecutive days that meet a criteria

[AaliiyahN82]

Hello, so I need to display a list of names that will meet 3 criteria: Absent for 2 days, 3 days, and over 3 days. And the days should be consecutive. Also I need to use a pivot table.

I have never used a calculated pivot table. Please help. Attached a sample workbook.

[SamT]

Is absent Friday and absent Monday two consecutive Absents?

One might start by sorting the table by Status >> Name >> Date.

One might also add Saturday and Sunday Dates with Status = NA or AO. This Status would also account for Authorized days Off.

Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality.

[p45cal]

At cell F7 in the attached, Power Query to Pivot table.
Change the data in Table1 and right-click the pivot and choose Refresh.
Not quite in the same format, for which you'd need to bring the data into the Data Model and use an additional measure in Power Pivot

[AaliiyahN82]

Is absent Friday and absent Monday two consecutive Absents?

One might start by sorting the table by Status >> Name >> Date.

One might also add Saturday and Sunday Dates with Status = NA or AO. This Status would also account for Authorized days Off.

Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality.


Hello SamT,

1. Is absent Friday and absent Monday two consecutive Absents?- Yes if their off is Saturday and Sunday
2. Unless this is a Homework assignment, which scenarios, are of course, not required to have a one to one relation with reality. -It's for work :D

[AaliiyahN82]

At cell F7 in the attached, Power Query to Pivot table.
Change the data in Table1 and right-click the pivot and choose Refresh.
Not quite in the same format, for which you'd need to bring the data into the Data Model and use an additional measure in Power Pivot


What's Power Pivot :banghead:
Do I need to install anything else?

[p45cal]

What's Power Pivot :banghead:
Do I need to install anything else?Depends on your version of Excel.
If you change the data and refresh, does it work?

[SamT]

1. Is absent Friday and absent Monday two consecutive Absents?- Yes if their off is Saturday and Sunday
Well... there are two possibilities:
1) No one ever works weekends or holidays,
2) sometimes someone works weekend or holiday days.

If 2), then Absent Friday and Monday, but worked Saturday does not fit the criteria
How is the Code/Solution supposed to determine?

These are called "Edge Cases (https://duckduckgo.com/?q=Programming%3A+Edge+Cases&t=ffcm&ia=web)." We can prepare for them now, or you will find them when someone complains to HR.