# Subtracting 30 minutes only if parameters are met...

#### Sailorzane

##### New Member
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.

 Date Log in Log Out Total Hours Monday ########## Tuesday ########## Wednesday 7:00 AM 6:48 PM 11:18 Thursday 7:59 AM 6:56 PM 10:27 Friday 7:09 AM 6:17 PM 10:38 Saturday 7:09 AM 6:39 PM 11:00 Sunday 7:55 AM 12:29 PM 4:04 Monday ########## Tuesday 6:59 AM 6:13 PM 10:44 Wednesday 7:03 AM 6:33 PM 11:00 Thursday 7:00 AM 7:05 PM ########## Friday 6:50 AM 7:16 PM ########## Saturday 7:29 AM 6:24 PM 10:25 Sunday 6:53 AM 5:55 PM 10:32 TOTAL 64.15

<tbody>
</tbody>

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)

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### JoeMo

##### MrExcel MVP
Try in D8 and copy down:

Code:
``=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

##### New Member
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

##### New Member
figured it out, sometimes I over think these things.

1,090,452
Messages
5,414,611
Members
403,536
Latest member
JEduardo