Counting total hours worked within m

[umass02]

(Title was supposed to say: Counting total hours worked within multiple days)

Is there anyway to calculate the # of work hours spent on a project given 2
seperate dates and times, taking into account a work day of 8-5 with an hour lunch?

For example, if I work 8-5, start a project on 8/3 at 9 and finished it on
8/7 at 4, the answer would be 22 work hours. Ideally, i'm trying to avoid having a lunch out/lunch in column or having to enter each day into a spreadsheet...

I'm thinking a datediff equation

Any ideas would be greatly greatly greatly appreciated.

Thanks!

[daddylonglegs]

That can certainly be done, although the lunch hour makes it trickier. What time is lunch?

[umass02]

Thanks for the reply. 12-1 would be lunch.

Got a hunch?

[daddylonglegs]

Assuming you have start time and date in A2 and end time and date in B2 then this formula will give the total hours

=(NETWORKDAYS(A2,B2)-1)/3+MOD(B2,1)-MOD(A2,1)-((MOD(A2,1)<1/2)-(MOD(B2,1)<1/2))/24

format result cell as [h]:mm

Note: NETWORKDAYS function is part of Analysis ToolPak add-in which you need to have installed (Tools > addins > tick "Analysis Toolpak" box)

I'm assuming that both start and end times will be within working hours, otherwise the formula may give the wrong result.

[umass02]

Thanks Daddy! You're a genious!

I think my only problem now is that if a project goes beyond 24 hours, the total # of work hours will reset back to zero (for obvious reasons in your formula). Is there a way to not have it reset?

Thanks again

[daddylonglegs]

As I said above - you need to custom format the cell containing the formula as [h]:mm.

This allows Excel to display "elapsed hours", rather than times, and therefore displays hours over 24 correctly