Microsoft KB Archive/87855

{|
 * width="100%"|

Excel: Calculating Work Hours for a Given Time Period

 * }

-

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)&&quot;:&quot;&TEXT(A8,&quot;00&quot;)

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 &quot;hh:mm&quot; 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.