Excel: Calculating Work Hours for a Given Time PeriodID: Q87855
|
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.
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<0,60+A2-A1,A2-A1))
A9:
A10: =SUM(A6,A7)&":"&TEXT(A8,"00")
"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 :
Last Reviewed: March 26, 1999