Deducting break from total hours worked

[robremsk01]

Hello,

I have an Excel sheet set up to automatically calculate the total hours worked for me. I enter in the time in and out, and the total hours is automatically calculated. The formula I am using for that is,

=IF(E90<D90,E90+1,E90)-D90

The cell that calculates the above formula is formatted to Time, 13:30

What I would like help with is how to deduct a half hour break from the total hours worked IF it is 6 hours or more and have it automatically calculate. I round the employee time either up to the nearest quarter or down depending whichever is closest, is there a way to have this factored in to the formula?

Thank you for your help, I very much appreciate it.

[daffodil11]

=if(if(e90<d90,e90+1,e90)-d90>=(1/4),if(e90<d90,e90+1,e90)-d90-(1/48),if(e90<d90,e90+1,e90)-d90)

1/4 = 6 hours

1/48 = 30 min

[Tony Valko]

=IF(E90<D90,E90+1,E90)-D90

Like this:

=MOD(E90-D90,1)

Do you want to round then subtract the break or subtract the break then round?

[robremsk01]

=if(if(e90<d90,e90+1,e90)-d90>=(1/4),if(e90<d90,e90+1,e90)-d90-(1/48),if(e90<d90,e90+1,e90)-d90)

1/4 = 6 hours

1/48 = 30 min

Thank you for your response. I tried entering the above equation and it did not work. Do I need to enter the 1/4=6 hours 1/48 = 30 min into the equation? What should I be formatting the cell to - general, numbers?

[robremsk01]

Like this:

=MOD(E90-D90,1)

Do you want to round then subtract the break or subtract the break then round?

This equation does not deduct a 30 minute break. It does not matter, either way the number of hours worked comes out to be the same whether you deduct the break before or after.

[daffodil11]

Excel stores dates as they relate to 1/1/1900 by count of days, and time is a decimal value as it relates to 1, which is one day.

A quarter of a day is literally the decimal .25, half hour is 1/48, and 15 minutes is 1/96.

Therefore, the decimal value 25.5 = 25 days, 12 hours from 1/1/1900 or 1/25/1900 12:00:00

If an end date and time minus the start date and time is larger than the decimal .25, then the length of time is greater than 6 hours.


Using this you can build an IF that checks if time worked is 6 hours or more, and then act upon it.


=if(if(e90<d90,e90+1,e90)-d90>=(6 hours),if(e90<d90,e90+1,e90)-d90-(30 minutes),if(e90<d90,e90+1,e90)-d90)


The whole equation breaks down to: IF time worked >= 6 hours, subtract 30 minutes, IF not, don't subtract 30 minutes

[Tony Valko]

That formula was to show you how to calculate the hours worked.

To deduct 30mins if the total is >6hrs...

D90 = time in
E90 = time out

=MOD(E90-D90,1)-IF(MOD(E90-D90,1)>6/24,30/1440)

Format as h:mm