It is possible, but it involves a setting change that comes with a warning.

I recently did an Excel Date and Time webinar (click here to watch it) and a follow up question involved negative time. I thought it was impossible, until I did some research.

Negative time doesn’t make sense and in the normal course of Excel calculations negative time won’t display.

See the images below. Column A and B are inputs. I have different cells selected in each to show the simple formulas in place.

These have simple entry and calculations to determine the variance in hours worked against the Standard hours. No problems.

If we change cell G1 to 8:00 things get messy – see below.

Excel, in its standard form, does not display negative hours.

Setting change

Excel uses the 1900 date system, so the first day in Excel’s calendar is 1/1/1900.

There is an alternate calendar, the 1904 date system, where the first day is 1/1/1904.

This alternate date system allows negative time.

It’s use comes with a warning see bottom of post.

To change the setting click the File ribbon tab and choose Options (bottom left).

Click the Advanced Option and scroll most of the way to the bottom until you see the “When calculating this workbook“ section. As per the image below tick the Use 1904 date system.

Click OK and you are done, problem solved – see image below.

WARNING

This setting change affects the date calculations for this file. If you do not refer to this file from other files there is no issue, it’s all good, no downside. If you link to this file from other files then you may experience errors with data calculations between the files. If both files use the 1904 date system then you are OK.