Conditional Time differences....

[chpassage]

I am working on a work schedule and have come across a problem. I want to be able to add a condition to a formula that "If an empoyee works 6 or more hours in one day, then 1/2 an hour (00:30) will automatically be subtracted from the total for that day. (On the other hand, and it may be easier said this way) if the employee works less than 6 hours, I don't want the 1/2 hour to be taken out. I am currently using the formula =(B1-A1) where B1 is the "Ending Time" and A1 is the "Starting Time" --- The problem is that an employee doesn't work over 6 hours every day, and therefore doen't take a lunch period. The schedule may change from week to week or day to day. I appreciate any help!

 

[chpassage]

I figured it out..... Assuming A1=Time In & B1=Time Out......

<<< =IF(B1-A1<0.25,B1-A1,(B1-A1)-0.020833)*24 >>>

I was forgetting that I needed to use the decimal conversions for the time values....... 0.25 (6 hours) is my desired threshold, and 0.020833 is the numerical equivelent to 30 mins. This would change if the lunch period was different (ie:0.03125 for 45 mins or 0.041667 for an hour). Multiplying the entire condition by 24 allows me to see the result as say 7.50 hours instead of 7:30.....This cell should be formated "general" and have 2 decimal places to be displayed correctly.