Time breakdown function in Excel

**tonycm1** · 11-30-2007, 09:08 PM

I have an excel sheet that contains the following information:

Name Start End Duration
Joe Smith 3:26:55 23:49:06 20:22:11

If I wanted to break down (per Name) how many hours between 7am-5pm people spend in a 4th column, any suggestions how I can do this?

**royUK** · 12-01-2007, 06:49 AM

Tony

Please read the Rules & pay attention to cross posting. This applies to all forums, not just here.

ross posted

http://vbaexpress.com/forum/showthread.php?t=16445

**DeeDeeSoft** · 12-01-2007, 07:02 AM

Your formula in column 4 should read:

=IF(AND(B1>=TIME(5,0,0),C1<=TIME(17,0,0)),C1-B1,"")

Be sure to format column for to "Time".

**DeeDeeSoft** · 12-01-2007, 07:51 AM

I would add two more columns as below:

Employee, Time in,Time out, Time before 7 AM, Time After 5 PM, Time Between 7 AM and 5 PM

Formula for "Time Before 7 AM" (Col D) would be:

=IF(TIME(7,0,0)-B2<0,0,TIME(7,0,0)-B2)

Formula for "Time after 5 PM" (Col E) would be:

=IF(C2-TIME(17,0,0)<0,0,C2-TIME(17,0,0))

Formula for "Time between 7 AM and 5 PM" (Col F) would be:

=C2-B2-(D2+E2)

This subtracts out any overtime before 7 AM or after 5 PM.

**tonycm1** · 12-01-2007, 08:12 AM

I just wanted to apologize for cross-posting... I didn't read the rules and wasn't aware that this was frowned upon so for that, I apologize.

in response to Dee's suggestion, I had something similar going but the problem lies if someone does an "overnight shift".... doesnt seem to account for that

A gentleman by the name of Barry helped me with this dileama and showed me that by adding a 5th column with the following formula:

=(B2>C2)*MEDIAN(0,C2-7/24,5/12)+MAX(0,MIN(17/24,C2+(B2>C2))-MAX(7/24,B2))

This would calculate the amount of hours worked between 7am-5pm.

Granted, it took me 20minutes to decipher this... but now i understand it a lot better and can apply this type of reasoning for future problems