Nested IF statements to analyze time data

[Darren_workforce]

Hello,

My sheet has shift start times down Column B and shift end times down Column C. (Column A has the names of staff). We open at 8:00:00am and close at 6:00:00pm. I have the following statement in Column D but am missing crucial pieces and am just uncertain where/what to add. I would like to generate 1 of 3 results in Column D based on the start/end times: (Opener, Closer, Mid).

If the time in Column B (start) is earlier than 9:00:00am, an agent is considered an Opener (as long as their end time is earlier or equal to 5:45:00pm).
If the time in Column C (end) is later than 5:45:00pm, the agent is considered a Closer (as long as their start time is later or equal to 9:00:00am).
However, if neither of these conditions are true and the agent arrives after 9:00:00am but also leaves prior to 5:45:00pm, they're considered a Mid-shifter.

=IF(AND($B2<=TIMEVALUE("9:00:00"),$C2<=TIMEVALUE("18:00:00")),"Opener","Closer")

I get tripped up with IF statements so any help would be greatly appreciate. Thank you in advance!!

 

[Peter_SSs]

What about you give us with XL2BB 6 or 8 varied rows of data with the results manually filled in and explain again in relation to that specific sample data?

 

[Darren_workforce]

I'm not certain how to generate the format you're requesting (apologies for that) but would a screenshot work?

If the time in Column B is earlier than 9:00:00am, an agent is considered an Opener (but they would also require an end time earlier than or equal to 5:45:00pm).
If the time in Column C is later than 5:45:00pm, the agent is considered a Closer (as long as their start time is later than or equal to 9:00:00am).
However, if neither of these conditions are true and the agent arrives after 9:00:00am but also leaves prior to 5:45:00pm, they're considered a Mid-shifter.

Rows 6:9,14:15 - Column D is incorrect because the start time is after that of an Opener (< 9:00:00am) and their end times are earlier than a Closer (> 5:45:00pm) so they would be a Mid.

I know I'm missing the "Mid" portion of the formula but I'm just not certain where it should be applied in the actual formula.

=IF(AND($B2<=TIMEVALUE("9:00:00"),$C2<=TIMEVALUE("18:00:00")),"Opener","Closer")

 

[Peter_SSs]

I'm not certain how to generate the format you're requesting

Did you follow the link that I provided?

but would a screenshot work?

Not so good because we cannot copy from that.

(but they would also require an end time earlier than or equal to 5:45:00pm).

What happens if that is not the case? Can't see any examples with B before 9am and C after 5:45pm

If the time in Column C is later than 5:45:00pm, the agent is considered a Closer (as long as their start time is later than or equal to 9:00:00am).

Sam as above, what happens if C > 5:45pm and B < 9:00am? Can't see any examples like that.

Rows 6:9,14:15 - Column D is incorrect

If you filled in all results manually with the results you do want (as requested) and not with a formula that does not do what you want it would be better.

 

[Darren_workforce]

Did you follow the link that I provided?
I did but when attempting to run the add-in I get a popup: This file type is not supported in Protected View. It's a work PC so it may be locked down by my IT team.

Not so good because we cannot copy from that.
I know it's asking more of those offering help but can a variation be manually created on your end to simulate my file with the data I provided.


What happens if that is not the case? Can't see any examples with B before 9am and C after 5:45pm
Nobody has a shift longer than 8 hours so there wouldn't ever be a need to have a start time earlier than 9:00am AND an end time after 5:45pm. If that ever did occur, I could always just manually enter the desired results into the respective D cell.

Sam as above, what happens if C > 5:45pm and B < 9:00am? Can't see any examples like that.
No examples as explained previously.


If you filled in all results manually with the results you do want (as requested) and not with a formula that does not do what you want it would be better.

The results for the most part are what I'm looking for but for those that are wrong, they would all be considered Mid-shifters due to not having neither a start nor end time that falls within the ranges mentioned previously.

 

[Darren_workforce]

I feel like I setup the formula correctly. I tested it and it works for the most part. However, when no time data is provided (the blank rows), it should return a value of "" (nothing). What's wrong below?

=IF(AND($B2>TIMEVALUE("8:30"),$C2<TIMEVALUE("17:45")),"Mid",IF(AND($B2>TIMEVALUE("8:30"),$C2>=TIMEVALUE("17:45")),"Closer",IF(AND($B2<=TIMEVALUE("8:30"),$C2<TIMEVALUE("18:00")),"Opener","")))

 

[RobOrBob]

=IF(OR($B2="",$C2=""),"",IF(AND($B2<TIMEVALUE("9:00"),$C2<=TIMEVALUE("17:45")),"Opener",IF(AND($C2>TIMEVALUE("17:45"),$B2>=TIMEVALUE("09:00")),"Closer",IF(AND($B2>TIMEVALUE("9:00"),$C2<TIMEVALUE("17:45")),"mid","?"))))