totn Excel

MS Excel 2003: Perform time calculations (Example #1)

Question: In Microsoft Excel 2003/XP/2000/97, I have a spreadsheet that is being used as a time card with the following setup:

Time In Lunch Hrs Time Out Work at home Tot Hrs
7:45 AM 1.0 4:30 PM 2.0  

I want to subtract the two time values (Time Out from Time In) and then subtract the number of hours for lunch and add to the result, the number of hours worked from home to get a total number of work hours.

I can't figure out how to subtract values from the time result. How can I do this?

Answer: You should be able to create a formula to add or subtract time values.

Let's look at an example.

Download Excel spreadsheet (as demonstrated below)

Below we have an Excel spreadsheet. In columns A and C, we have time values. Whereas, in columns B, D, and E, we have number values.

Microsoft Excel

Make sure that you've got the columns formatted correctly. To verify this, highlight a column that you want to view the format for, then under the Format menu, select Cells.

When Format Cells window appears, make sure that the correct format is selected.

Microsoft Excel

Now to calculate the total hours, we've used the following formula:

=((C2-A2)*24)-B2+D2

This formula subtracts the two time values (Time Out - Time In). It then multiplies by 24 to find the difference in hours. Then subtracts the Lunch Hrs and adds the Work at Home Hrs. This gives us our Total Hrs.