# Subtracting time at the end of an IF statement.

#### moko

##### New Member
Hi to all,

I am a novice requesting someone's help to make sense of what I have to do to attach a subtraction of time to the following IF statement, so that if the time worked is over 6 hours then a 30 minute lunch break is deducted from the total time spent at work - eg. if I spend 8 hours at work I end up getting paid for only 7:30.

Code:
``=IF(H13>G13,H13-G13,IF(H13<G13,(H13+12)-G13,IF(ISERROR(H13-G13),G13,)))``

This gives me the total hours at work, but I'd like to deduct the 30 minute lunch break from the total within the same formula if that's possible.
The error statement is for the odd text input, the cells should all represent times.
I have tried to do some things to it, but keep getting problems.

Any suggestions will be greatly appreciated.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### mortgageman

##### Well-known Member
Hi to all,

I am a novice requesting someone's help to make sense of what I have to do to attach a subtraction of time to the following IF statement, so that if the time worked is over 6 hours then a 30 minute lunch break is deducted from the total time spent at work - eg. if I spend 8 hours at work I end up getting paid for only 7:30.

Code:
``=IF(H13>G13,H13-G13,IF(H13<G13,(H13+12)-G13,IF(ISERROR(H13-G13),G13,)))``

This gives me the total hours at work, but I'd like to deduct the 30 minute lunch break from the total within the same formula if that's possible.
The error statement is for the odd text input, the cells should all represent times.
I have tried to do some things to it, but keep getting problems.

Any suggestions will be greatly appreciated.

Try this: (untested)
Change
Code:
``h13-g13``
to
Code:
``h13-g13-((h13-g13)>6)*.5)``

and similar, change
Code:
``(H13+12)-G13``
to
Code:
``(h13+12)-g13 - (((h13+12)-g13)>6)*.5``

#### barry houdini

##### MrExcel MVP
hello moko,

Your current formula is a little flawed.

The ISERROR check has to be first otherwise the formula will generate an error, also your 12 should be a 1 otherwise you might get the correct number of hours displayed but the underlying value will include extra time (11 days or 264 hours) which could be a problem if you want to sum total hours for a week, for instance.

Try this formula to also deduct ½ hour when the shift is longer than 6 hours

=IF(ISERR(H13-G13),G13,MOD(H13-G13,1)-(MOD(H13-G13,1)*24>6)/48)

format result cell as [h]:mm

#### moko

##### New Member
Thank you both very much I shall use both of your answers to work through my problem - and I'm very grateful for the help & education.

Replies
1
Views
206
Replies
0
Views
157
Replies
1
Views
138
Replies
0
Views
295
Replies
6
Views
366

1,191,718
Messages
5,988,267
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back