Pivot table un grouping dates

[mark hansen]

I have searched across multiple sites and found how to groups dates into Days, Months, Quarters and Years, but not how to ungroup multiple entries on the same day.

My data is set up in a table and each line is a specific evaluation for a specific student performing a specific task. Each evaluation has a student, task, scores on parts of the task, and a calculated column to average the score for the line (or specific evaluation). Each student can have multiple evaluations on the same task, in the same day.

In the pivot table, I have filters on Student, and slicers for task... And the date of the task in rows. When a Student performs the same task 2-3 times in one day, they get an evaluation for each time, so, those rows will have the same student, same task, same date but average score for each task can be different. When it goes in the pivot table, the rows will have only one row per date, and the score number for the date is the average of the average score for the day.

I need to have 3 rows in the pivot table (for that day), one for each evaluation. As the pivot table is filtered on the Student, and evaluation, I need the pivot table to have 3 rows, each with the same date and the 3 average scores (of course along with the other times they performed the same task on other days.) This will show the improvement (hopefully) related to each TIME they did the task, not each DAY they did the task.

Any help is appreciated.
Mark Hansen

 

[Misca]

The easiest way to accomplish this would be to use a column to identify the evaluations (numbers. letters, names). This way you could always ungroup the dates using that column value.

 

[mark hansen]

Thanks for the idea Musca. I guess a unique time the task was evaluated would also do the trick.

 

[Misca]

The problem with time in Excel is it's just a fraction of a number (dates being the integer part of the number). But if it's just time not time & date, then maybe. I mean I'm sure it would work but does it add any extra value to know this sample was taken at 6:39 where as something like "morning" might be a bit more useful. Of course you can get to the part of day level from the actual time but that would require an additional step.

And it doesn't have to be "unique" per se: It's enough to know the same student won't get the same value twice on the same day.

 

[mark hansen]

Thanks, I don't really need the time they completed the task (if I did, it would help solve the problem).... So I'm using a system generated time at the time they enter the information. As long as they are not too quick in entering evaluations, the system generated times should be a minute apart... So tasks done on the same day (which I need to be accurate) will have a system generated time of entry.

Thanks for your ideas.