Subtracting 30 minutes only if parameters are met...

[Sailorzane]

I have a time sheet calculator that i developed to calculate summations for me in respect to total hours worked for an individual. I stumped myself and believe I am over thinking the equation. Essentially i need it to subtract 30 minutes for a break if certain conditions are met, 8 hours or more worked, and if the log in and log out cells are populated. Thank you for your help.

[TABLE="width: 303"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Log in [/TD]
[TD]Log Out[/TD]
[TD]Total Hours [/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD]##########[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD]##########[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]7:00 AM[/TD]
[TD]6:48 PM[/TD]
[TD]11:18[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]7:59 AM[/TD]
[TD]6:56 PM[/TD]
[TD]10:27[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]7:09 AM[/TD]
[TD]6:17 PM[/TD]
[TD]10:38[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]7:09 AM[/TD]
[TD]6:39 PM[/TD]
[TD]11:00[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]7:55 AM[/TD]
[TD]12:29 PM[/TD]
[TD]4:04[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD]##########[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]6:59 AM[/TD]
[TD]6:13 PM[/TD]
[TD]10:44[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]7:03 AM[/TD]
[TD]6:33 PM[/TD]
[TD]11:00[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]7:00 AM[/TD]
[TD]7:05 PM[/TD]
[TD]##########[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]6:50 AM[/TD]
[TD]7:16 PM[/TD]
[TD]##########[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]7:29 AM[/TD]
[TD]6:24 PM[/TD]
[TD]10:25[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]6:53 AM[/TD]
[TD]5:55 PM[/TD]
[TD]10:32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD]64.15[/TD]
[/TR]
</tbody>[/TABLE]


Monday is A8 and the total is D23.
The formula in the first total hours cell is: =(C8-B8+IF(MOD(C8-B8,1)<0.5,(B8>C8),-(C8>B8)))-TIME(0,30,0)

 

[JoeMo]

Try in D8 and copy down:

=IF(OR(B8="",C8=""),"",IF(MOD(C8-B8,1)*24>=8,(MOD(C8-B8,1)-TIME(0,30,0))*24,MOD(C8-B8,1)*24))

Format col D as a number with as many decimal places as you want.

 

[Sailorzane]

That worked, I cannot figure out the placement of the math for a simple conversion to hours and minutes, knowing that i multiply the total by 60 then divide that by 1440 it will give me hours and minutes as long as the cell is formatted for that, where in the world do I place that in the formula?

 

[Sailorzane]

figured it out, sometimes I over think these things.