How to turn Zoom attendance reports into an course attendance report

Export the usage report for each class session and combine into a single spreadsheet

Follow the instructions on this page for generating reports for each of your class sessions: How do I generate a Zoom attendance sheet in Canvas?

Copy the data from each report so that you have a single, long spreadsheet with all of the attendance data from all of your zoom sessions.

Highlight all of the data in your spreadsheet, including the column titles

In the Excel menu select "Data" and then "Summarize with a Pivot Table"

Depending on your computer system and version of Excel, this may look slightly different.

Click "OK" on the "Create PivotTable" menu.

You don't need to change any of the settings here.

Click anywhere in the PivotTable box to edit

A new tab is added to your spreadsheet that will be blank except for the empty PivotTable box. Click anywhere in that box to reveal the PivotTable field editor.

Select and assign the PivotTable Fields

On the PivotTable Fields window, select "Name", "Join Time", and "Duration", which will al be placed in the "Values" section below. Drag "Name" into the "Rows" section and "Join Time" into the "Columns" section.

Group the columns by day

Your PivotTable should now have a list of student names on the left, with a column for each date and time that students joined the Zoom meeting. To group the columns by day, right click any of the dates at the top of the columns and select "Group".

On the "Grouping" menu that pops up, select "Days" and click "OK".

There should now be one column for each day. If a student joined multiple times (such as if they lost connection and had to re-join) Excel will add up the duration from each time the student joined that day.

Clean up duplicate  names

You will likely have some duplicate names in your data, because even slight changes to a student's name between sessions will create a separate row in the PivotTable. Whenever you see a duplicate in the PivotTable, go back to the tab in the spreadsheet that has the data and replace all of the variations of a student's name with the same data.

For Example: If I were combining the two "David Grogan" entries above, I would replace all instances of "David Grogan (TTS) (David Grogan)" in the original data with "David Grogan (TTS)".

After cleaning up your duplicates, go back to the PivotTable, right-click the table, and select "Refresh". The duplicates should disappear and the attendance data will be merged into one row.

This step can be done before creating the PivotTable, but sometimes it is easier to discover the duplicates on the PivotTable than in a large data set.

Use the PivotTable data to generate your attendance grades

Because each instructor handles attendance grading differently, there is no single way to use this data to generate a grade. One useful formula is the "COUNTIF" function in Excel, which you can use to count cells that meet certain criteria.

For example: If my class periods were 50 minutes, I might count 45 minutes of duration as full attendance. In the screenshot above, I have entered the formula =COUNTIF(B5:D5, ">45") into the F5 cell. What this will do is count all of the cells from B5 to D5 that have a number greater than 45. After creating the formula in one cell, you can paste it into the rest of the cells and it will update for each row accordingly.

After you have calculated your attendance grade you will need to enter those grades into your gradebook of record.