MS Excel 2003: Perform time calculations (Example #2)
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 | Time Out (for lunch) |
Time In (back from lunch) |
Time Out | Total Hours |
|---|---|---|---|---|
| 7:45 AM | 11:30 AM | 1:00 PM | 4:30 PM |
I want to calculate the time worked based on the Time In and Time Out values. How can I do this?
Answer: You should be able to create a formula that calculates the total hours worked.
Let's look at an example.
Download Excel spreadsheet (as demonstrated below)
Below we have an Excel spreadsheet. In columns A through D, we have our Time In and Time Out values. In column E, we want to calculate the total hours worked.
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.
Now to calculate the total hours, we've used the following formula:
=((B3-A3)+(D3-C3))*24
This formula subtracts the two (Time Out - Time In) values and then adds them together. It then multiplies by 24 to find the difference in hours. This gives us our Total Hours.
Advertisements