Automatic Shift Allocation

[Vimal_Gaur]

Attaching a sheet containing Shift Name in Column C and Shift Condition in Column C.

Now, I need that shift should automatically be allocated in Column F according to Time shown in Column E. 

[#2]

@Vimal_Gaur 

I think after looking at your table of times and conditions, that:

• a relatively simple use of VLOOKUP, XLOOKUP, or INDEX and MATCH could give the desired results, BUT
• you could (and should) make the conditions clearer before attempting to program column F.
  • Specifically, you give the "exact shift start time" for each shift, A, B and C. But you don't give the exact shift end time. That would help, for starters.
  • Then answer the question, what exactly is meant by the "-H"  and other extensions. They appear to account for an additional 1.5 to 3 hours after what appear to be end exact shift end times

But I'm making assumptions--you know what those extensions mean and if you could articulate the "rules," then we might well be able to come up with programmatic "rules" that would help govern the assignment of shift names.

[#3]

@mathetes 

@mathetes Thanks for your efforts & suggestions:

I'm attaching herewith a new sheet with exact problem.

• The "IF(AND" was also related to this sheet only. I thought "False" is because of limitation of nested if(and, but later on figured out that it is due change of day.

Plz peruse the sheet and give your expertise to resolve the "False" problem and also suggest any new short formula.

[#4]

@Vimal_Gaur 

You could update your IF to fulfill the False return to display desired text.  Currently, FALSE is being returned because the False argument of the last IF is empty.  The formula in G2 does not recognize FALSE as literal text because it's a logical FALSE comparable to using =FALSE().

=IF(AND(F2>=$B$2,F2<$C$2),$A$2,IF(AND(F2>=$B$5,F2<$C$5),$A$5,IF(AND(F2>=$B$6,F2<$C$6),$A$6,IF(AND(F2>=$B$7,F2<$C$7),$A$7,IF(AND(F2>=$B$10,F2<$C$10),$A$10,IF(AND(F2>=$B$11,F2<$C$11),$A$11,IF(AND(F2>=$B$12,F2<IF(F2>$C$12,(F2+$C$12),$C$12)),$A$12,IF(AND(F2>=$B$15,F2<$C$15),$A$15,IF(AND(F2>=$B$16,F2<$C$16),$A$16,"Text")))))))))

[#5]

@Vimal_Gaur 

Here's a formula that works for your "FALSE" reply. I added one set of conditions, highlighted in red below. This formula takes advantage of the IFS function, rather than nesting all levels of IF(AND( ... and is easier to read (and debug) if you enter it as follows

=IFS(

AND(F2>=$B$2,F2<$C$2),$A$2,
AND(F2>=$B$5,F2<$C$5),$A$5,
AND(F2>=$B$6,F2<$C$6),$A$6,
AND(F2>=$B$7,F2<$C$7),$A$7,
AND(F2>=$B$10,F2<$C$10),$A$10,
AND(F2>=$B$11,F2<$C$11),$A$11,
AND(F2>=$B$12,F2>$C$12),$A$12,
AND(F2>=$B$15,F2<$C$15),$A$15,
AND(F2>=$B$16,F2<$C$16),$A$16,
AND(F2>0,F2<=0.16),$A$13
)

I still think you could make things clearer if you were to pay attention to my questions in my first reply. And a still simpler formula could be developed.