Evaluating Work Shifts

  • Thread starter Thread starter wutzke
  • Start date Start date
W

wutzke

I have a formula to looks at a work schedule with values like '11 - 8'
& '8 - 5'


=IF(ISERROR(SEARCH("*-*",D3)),
0,IF(AND(VALUE(LEFT(D3,SEARCH("-",D3)-1))>12,VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))>12),"X-Hour",IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))-1<12,12-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0),0-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(0-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0))))

Sometime an employee specifically request a shift. That shift is
entered as ''8 - 2 ®'. That totally blow the formula above. I'd still
like to evaluate the length of the shift in this format as well as
count the number of times '®' occurs
 
Hi Michael

One way
Choose a column outside of your range , maybe column X and use
=SUBSTITUTE(D3,CHAR(174),"")
Then use X3 in your formula rather than D3
 
If you're willing to "bend" a little, this would work for you.

Say you use this format instead:
'11 - 8
'11 ® 5

Then this revision of your formula should work:

=IF(AND(ISERR(FIND("-",D3)),ISERR(FIND("®",D3))),0,
IF(AND(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1)>12,
--RIGHT(D3,LEN(D3)-MIN(FIND({"-","®"},D3&"-®")))>12),"X-Hour",
IF(12-(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1))+
(--RIGHT(D3,LEN(D3)-MIN(FIND({"-","®"},D3&"-®"))))-1<12,
12-(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1))+(--RIGHT(D3,LEN(D3)-MIN(FIND(
{"-","®"},D3&"-®"))))
-IF(12-(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1))+
(--RIGHT(D3,LEN(D3)-MIN(FIND({"-","®"},D3&"-®"))))>4,1,0),
0-(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1))+(--RIGHT(D3,LEN(D3)-MIN(FIND({
"-","®"},D3&"-®"))))
-IF(0-(--LEFT(D3,MIN(FIND({"-","®"},D3&"-®"))-1))+
(--RIGHT(D3,LEN(D3)-MIN(FIND({"-","®"},D3&"-®"))))>4,1,0))))

I shortened your original formula slightly by eliminating the Value()
function and changing Search() to Find(),
so that when the other functions were added, it wasn't quite so long.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


I have a formula to looks at a work schedule with values like '11 - 8'
& '8 - 5'


=IF(ISERROR(SEARCH("*-*",D3)),
0,IF(AND(VALUE(LEFT(D3,SEARCH("-",D3)-1))>12,VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))>12),"X-Hour",IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))-1<12,12-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0),0-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(0-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0))))

Sometime an employee specifically request a shift. That shift is
entered as ''8 - 2 ®'. That totally blow the formula above. I'd still
like to evaluate the length of the shift in this format as well as
count the number of times '®' occurs
 
That formula seems ridiculously complex. can you explain in rule terms what
it does, maybe we can simplify matters completely.

--
__________________________________
HTH

Bob

I have a formula to looks at a work schedule with values like '11 - 8'
& '8 - 5'


=IF(ISERROR(SEARCH("*-*",D3)),
0,IF(AND(VALUE(LEFT(D3,SEARCH("-",D3)-1))>12,VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))>12),"X-Hour",IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))-1<12,12-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(12-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0),0-
VALUE(LEFT(D3,SEARCH("-",D3)-1))+VALUE(RIGHT(D3,LEN(D3)-
SEARCH("-",D3)))-IF(0-VALUE(LEFT(D3,SEARCH("-",D3)-1))
+VALUE(RIGHT(D3,LEN(D3)-SEARCH("-",D3)))>4,1,0))))

Sometime an employee specifically request a shift. That shift is
entered as ''8 - 2 ®'. That totally blow the formula above. I'd still
like to evaluate the length of the shift in this format as well as
count the number of times '®' occurs
 
Back
Top