Excel: Display and calculate negative times

This practical tip shows you how you can display and calculate negative times in Excel. Before doing this, you have to make changes in the settings in Excel.

Preparation: Display negative times in Excel

To work with negative times in Excel, you first have to make a setting. If you can already display negative times, read on in the next paragraph.

Step 1:

Start Excel and select the "Options" under "File" in the top left.

Step 2:

Go to the "Advanced" category on the left and scroll down to the "When calculating this workbook" area.

Step 3:

There, activate the "Use 1904 date values" check box and confirm the change with "OK". Now Excel can display negative times correctly.

Instructions: Expect negative times in Excel

With the setting activated in the first section, you can now calculate negative times in Excel. If you add up several hours, the time display starts again at 0 for a total of more than 24 hours.

Step 1:

You can prevent this by changing the number format. To do this, mark the affected cells or the entire folder with the key combination [Ctrl] + [A].

Step 2:

Right-click on the selected cells and select "Format cells" from the context menu.

Step 3:

Go to the "User-defined" category on the left and enter the following value for "Type": "[h]: mm: ss". If you confirm with "OK", the total hours are displayed correctly.