Negative Time in Excel

When you're working on a trial, an Excel spreadsheet is a great way to keep track of the time of testimony. You mark in two columns the time at which a witness begins and ends his or her testimony. The duration of the testimony can easily be obtained by subtracting the former figure from the latter. You may also want to keep track of how the actual time differs from the time allotted to the witness. Here you may run into a problem when witness testimony falls short of the maximum assigned.

In this example we see that for the entries where the witness testified in less time than they were allowed there are number signs listed in the 'Difference' column. Columns B and C are formatted for Time entries, and column E and F are custom formatted for h:mm. A problem arises when you try to subtract a higher time figure from a lower one.

You can fix this problem by going (in Excel 2016) to File . . . Options. In the Advanced section, scroll down to the 'When calculating this workbook' section and check the box for 'Use 1904 date system'

Click OK and you'll see that the formatting error in column F has been corrected.