Subtracting time at the end of an IF statement.

[moko]

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.

=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.

 

[mortgageman]

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.

=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

h13-g13

to

h13-g13-((h13-g13)>6)*.5)

and similar, change

(H13+12)-G13

to

(h13+12)-g13 - (((h13+12)-g13)>6)*.5

 

[barry houdini]

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]

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.