Excel: Calculating Work Hours for a Given Time Period

ID: Q87855

The information in this article applies to:

Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 3.x, 4.x, 5.0, 5.0a

SUMMARY

Using Microsoft Excel, you can write a formula to calculate the hours and minutes in a given period of time, based on an hourly workday. This can be useful for calculating time spent on a project.

MORE INFORMATION

The following example calculates elapsed time based on an 8 hour workday that runs from 8:00 AM to 5:00 PM with 1 hour off for lunch.

This formula uses DAYS360() which bases its calculation on a 360 day year (12 30-day months). For a more exact number of days you can use subtraction. For example, you could change A6 to read:

A6: =(INT(A2)-INT(A1))*A3

The comments in column B describe the values in corresponding cells in column A.


 A1: 6/1/92 8:00 AM      B1: Start time
 A2: 6/5/92 5:00 PM      B2: End time
 A3: 8                   B3: Number of hours per day
 A4: 1                   B4: Lunch/Break time (in hours)
 A5:
 A6: =ABS(DAYS360(A1,A2)*A3)
 A7: =ABS((HOUR(A2)-HOUR(A1))-A4+INT((MINUTE(A2)-MINUTE(A1))/60))
 A8: =MINUTE(IF(A2-A1&lt;0,60+A2-A1,A2-A1))
 A9:
A10: =SUM(A6,A7)&":"&TEXT(A8,"00")

The formulas in cells A6 and A7 calculate the number of hours in the time interval. The formula in cell A8 calculates any remainder in minutes. The formula in cell A10 combines the results of the three formulas, using concatenation to display the total time in "hh:mm" format.

NOTE: The results of this formula include weekends and holidays. In version 4.0 of Excel you can modify the forumla to exclude weekends and holidays.

To exclude weekends and holidays, replace the formula in cell A6 with:

A6: =ABS((NETWORKDAYS(A1,A2)-1)*A3)

NETWORKDAYS() returns the number of full working days (excluding weekends and holidays) between two dates. This function is not available in versions earlier than Excel 4.0.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 1, 95, 222, 244, 275, 284

"Microsoft Excel Function Reference," version 3.0, pages 1, 46, 123, 133, 152

Additional query words: 7.00 elapsed change difference 3.0 3.00 4.0 4.00

Keywords :
Version :
Platform :
Issue type :