tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

MS Excel: Perform negative time calculations in Excel 2003/XP/2000/97

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:

Microsoft Excel

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)

Microsoft Excel

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.