Forum Discussion
Automatic Shift Allocation
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.
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.
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.
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")))))))))
