---

MS Excel 2003: Perform negative time calculations

Question: In Microsoft Excel 2003/XP/2000/97, I have a spreadsheet that is being used as a flexi-timesheet where you have standard hours worked. So, if you work 8 hours and the standard day is 7 hours and 24 minutes, you will have worked an extra 36 minutes. Some days you will work less than the standard hours and so will have a negative balance. Excel will not show these negative balances properly.

My spreadsheet is set up as follows:

Answer: Unfortunately, Excel will not let you express a formatted time value as a negative number. However, you can express your Balance column (ie: column J above) as the number of minutes instead of a time value.

Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Now to calculate the Balance column (ie: column J) to calculate the balance in minutes, we've used the following formula:

=(H5-I5)*24*60

This formula takes the difference between Hours Worked and Standard Hours, and then multiplies the result by 24 and then by 60. This converts the value into the number of minutes.

As you can see, the value in cell J9, now displays as -94 instead of an error.