IF THEN Formula Help

[Dihs]

I need help on creating a formula for a payroll timesheet.

I need to modify some current formulas to include a new function, where if the hours exceed or meet 10 hours, then a 1 hour lunch is automatically deducted, if the hours do not meet 10 hours then it remains a 30 minute deduction.

These are the current formulas used in the following 5 columns

-----A1---------------B1--------------C1------------------------D1------------------E1
Time Started------Time Left-------Total Hours------------------Lunch-----------------Adj
----*time in 24hr format*--------=MOD(A1-B1,1)---------=MOD(C1,1)-"0:30"-------(see below)


=INT(D1*24)+LOOKUP(MINUTE(D1),{0,10,20,30,40,50},{0,0.25,0.33,0.5,0.66,0.75})


(example)
--7:00--------------18:00------------11:00-------------------10:30----------------10:50


The formulas used currently allow me to enter the time in military format, have the total hours calculated, then calculated with the 30 minutes removed for lunch, then adjusted using the format the company rounds out hours with.

However, I need to include a formula that would automatically deduct 1 hour lunch if the work hours exceed or meet 10 hours, but a 30 minute would be deducted if 10 hours is not met. I was told an IF THEN function would be the method to use, however I am unfamiliar with such functions.

Any help would be greatly appreciated.

Thank you in advance.

Dihs

[NickyC]

Hi
try this in column C

=IF(C1<10/24,C1-"0:30",C1-"1:00")

[Dihs]

Thank you for your help, I put the formula into column D and it works perfectly.

Much appreciated.

Dihs

[Dihs]

One additional question with regards to the formula,

=IF(D1<10/24,D1-"0:30",D1-"1:00")

Is there a way to have it so that at 10hours it would still deduct 30 minutes, but at 10:01 hours it would deduct one hour?

How could I specify the exact time when a 1 hour deduction takes place?

Thanks for any help.

Dihs

[NickyC]

Yes - try

=IF(D1<=10/24,D1-"0:30",D1-"1:00")