Printable View
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.
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.
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
Quote:
Originally Posted by SamT
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
Quote:
Originally Posted by p45cal
What's Power Pivot :banghead:
Do I need to install anything else?
Depends on your version of Excel.Quote:
Originally Posted by AaliiyahN82
If you change the data and refresh, does it work?
Well... there are two possibilities:Code:1. Is absent Friday and absent Monday two consecutive Absents?- Yes if their off is Saturday and Sunday
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." We can prepare for them now, or you will find them when someone complains to HR.