Spreadsheet to Track Time Used for Each Party and for Each Examinaton

The Tip of the Night for September 4, 2021 discussed how to use the SUMIF formula in Excel to set up a spreadsheet to track the time each party uses in a trial. Tonight I have posted this spreadsheet:

Time of Testimony
.XLSX
Download XLSX • 25KB

. . . which you can use to track the time used by each party on each day with the SUMIFS formula, and the time that each witness examination takes using the SUMIF formula.


Begin by entering the date, witness examination description, party name, attorney name, start time, and end time in columns A to F. In column B, 'Witness' enter not only unique descriptions for each direct and cross examination, but also enter notes for lunch and time spent by the parties discussing procedural matters with the court. Be sure to enter a description for each examination or other event consistently with the same exact characters. In column C enter the name of the party conducting each examination, opening or closing, and also consistently enter notes for court and break time .



A simple subtraction formula in column G will calculate the amount of time used for each entry. Be sure to format this column for as h:mm:ss in the Custom setting.


Columns L to Q will track the cumulative time used by each party and the court (and used for breaks) by utilizing the SUMIFS formula. This formula works by searching for the values to be added up in first referenced column; then searching for a value given the second referenced column [this is case it looks for the date in L2 given in column A], and then only adding the values given at the end of formula listed in the third referenced column [so it looks for the party name given in M1 in column C].



The SUMIFS formula looks for two criteria in different columns and then only adds up the value given in a third column when the given values are matched in the other two columns. You want to use an absolute reference with dollar signs so the same column for the date is always referenced by pulling the formula to the right, and the same row is used for the party name, and then double dollar signs so each column which is searched for a value so it stays the same as the formula is pulled to the right.



The last row in this array uses a simple SUM formula to add up the time used by each party.


Columns U to Z are simply used to track the amount of time used that the parties agreed to. Manual entries should be made in each cell. It certainly seems as though each side always disagrees on exactly how much time they have taken up.



In column AC enter just once each description for an examination or other event used in column C. The SUMIF formula in column AD will then searcy for these values in column B and in the rows where the value is found add up the time in column G.