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.
What happens to more than 6.5 hours?
Less than 6.5, Nothing
Equal to 6.5, minus 15 minutes
More than 6.5, ??
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
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)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
Last edited by rwgrietveld; 02-12-2010 at 10:14 AM.
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.
Last edited by DonkeyOte; 02-12-2010 at 10:07 AM.
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 ?!
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
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 ?
It still deducts 15 mins for 6.5 hr shifts.
As it should... you said greater than 4.5 hours is a 15 min deduction... greater than 6.5 hours is 30 min deduction
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks