editing a formula that automatically deducts breaks

[lengjay]

Hi,

I have been using this formula in my spreadsheet.

=IF(OR(ISTEXT(I5),ISTEXT(J5)),0,((MOD(J5-I5,1)-LOOKUP(MOD(J5-I5,1),{0;0.197916666666666;0.28125},{0;0;0}))*24))

It takes the start time in I and finish time in J.....

What I require is for a shift which is 6.5 hours long or less to not deduct any break, however when I create a shift with which is 6.5 hours in length, it deducts 15mins.

Please help!

J.

[rwgrietveld]

What happens to more than 6.5 hours?

Less than 6.5, Nothing
Equal to 6.5, minus 15 minutes
More than 6.5, ??

[DonkeyOte]

You seem to be saying

shift duration <= 6:30 means no break else 0:15 break to be deducted

in which case:

=IF(COUNT(I5:J5)<2,0,(MOD(J5-I5,1)-"00:15"*(MOD(J5-I5,1)>0+"6:30"))*24)

[rwgrietveld]

Great DO, I like the approach.

I just have to get this into my system:
MOD(J5-I5,1)>(--"6:30") or
MOD(J5-I5,1)>0+"6:30" or
MOD(J5-I5,1)>1*"6:30"

It's more readable than
MOD(J5-I5,1)>6.5/24

[DonkeyOte]

Typo there I think Ricardo - if using + you would use 0 rather than 1

(you could equally use TIME function and avoid coercion requirement)

As a general rule I personally prefer to use Time (coerced strings, Time Function) rather than using decimal interpretations given the levels of significance of Time values - others I'm sure take the opposite view.

[rwgrietveld]

The thing is DO it might go wrong in case of

IF(A="06:30", This would be fine
IF(A=TIME(6,30,0), also fine

but
IF(A=6.30/24, could go wrong as excel is strange in rounding

haven't tested this but it might ?!

[lengjay]

Thanks for the responses guys!

If less than or equal to 6.5 hours - no deduction

More than 6.5 hours - 30 mins deduction




Would you also be able to create a formula for

0 - 4.5 hrs no deduction
4.5 - 6.5 hrs 15 mins deduction
6.5 + 30 mins deduction

I realise that's what I have at present, but just perhaps in more tidy code


Thanks for your help guys! :D

[DonkeyOte]

OK ... well there's some overlap there - we'll assume based on initial topic:

0 <= 4.5 etc...

Please Login or Register  to view this content.

does that work for you ?

[lengjay]

It still deducts 15 mins for 6.5 hr shifts.

[DonkeyOte]

As it should... you said greater than 4.5 hours is a 15 min deduction... greater than 6.5 hours is 30 min deduction