Subtracting 30 minutes only if parameters are met...

Sailorzane

New Member
Joined
Oct 23, 2019
Messages
3
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.

DateLog in Log OutTotal Hours
Monday##########
Tuesday##########
Wednesday7:00 AM6:48 PM11:18
Thursday7:59 AM6:56 PM10:27
Friday7:09 AM6:17 PM10:38
Saturday7:09 AM6:39 PM11:00
Sunday7:55 AM12:29 PM4:04
Monday##########
Tuesday6:59 AM6:13 PM10:44
Wednesday7:03 AM6:33 PM11:00
Thursday7:00 AM7:05 PM##########
Friday6:50 AM7:16 PM##########
Saturday7:29 AM6:24 PM10:25
Sunday6:53 AM5:55 PM10:32
TOTAL64.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)
 

Some videos you may like

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
Joined
May 26, 2009
Messages
16,692
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
Joined
Oct 23, 2019
Messages
3
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?
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top