Minus Times in Excel 1

[dannyw]

I have built up a simple spreadsheet for my colleagues to enter their times in and out which adds them up to give how many hours per day, per week and hence per month they have worked so that they can claim flex-time. However if they owe hours rather than being owed, Excel shows an error as it won't allow the result of the calculation expressed in hours and minutes to be negative. Is there any way to allow Excel to show times as minus amounts?

 

[Hasit]

By default, Excel uses a date system that begins with January 1, 1900 and therefore any negative time value falls before this which in turn is invalid (as you have found).

You need to use the optional 1904 date system. Select Tools, Options, click the Calculation tab, and check the 1904 date system box to change the starting date to January 2, 1904.

Then ensure that the cell you wish to show the negative time value in is formatted as [h]:mm

HTH

 

[xlhelp]

You can either use the 1904 date system or use a fromula to indicate negative times.

To use the 1904 system, go to Tools, Options, Calculation, and check "1904 date sytem"

The formula would be
=TEXT(ABS(B2-B3),"-h:mm:ss&quot
where B2 is earlier than B3