Shift Timing Challenge – Solution

Last week I posted about the Shift Timing Challenge. The problem came from one of our readers, Malcolm. He was finding it challenging to classify the shift timing in one of the following 3 categories

1. Between 10 PM – 5:59 AM you should write Shift 1
2. Between 6 AM – 1:59 PM you should write Shift 2
3. Between 2 PM – 9:59 PM you should write Shift 3

Along with my solution (that I will share with you now) I got some pretty interesting solutions from our readers.

 

Solution Using VLOOKUP approximate match

Step 1. Create a dummy column and arrange the time in ascending order

Notice that

1. The time has been arranged in the ascending order
2. The second column contains the Shift Number. I’ll explain the logic of writing the shift numbers in this order in a while

Let’s continue..

 

Writing a VLOOKUP to find the correct shift

Step 2: Apply VLookup using Approximate match

I am writing an approximate match VLOOKUP to get the shift number. Please note that

1. The last part of VLOOKUP is 1 or TRUE that means approximate match
2. The approximate match works when your time (or any value) is arrange in the ascending order in the lookup table

 

How Excel reads approximate match VLOOKUPS

This is how VLOOKUP (when used in approximate match) will work

Lets say if VLOOKUP is looking up for 9:30 am. This is how it will work

1. It is going to look for the cut off point larger than 9:30 am (which is 2 pm in our Vlookup table)
2. Then VLOOKUP is going to jump back to the previous value (which is smaller than 9:30 am i.e. 6 :00 am)

 

Another Example where VLOOKUP is trying to find 10: 30 pm

1. It will lookup for the cut off point larger than 10:30 pm (which is not there since our largest time value is 10 pm)
2. It is going to jump back to 10 pm which is less than 10 : 30 pm

 

Notice that our Shift 1 appears twice because it starts at 10 pm and goes up-till 6 am. That is the reason why it has been mentioned twice in the VLOOKUP table

 

 

DOWNLOAD THE SOLVED EXCEL FILE HERE FROM BELOW

A big shout-out to the heros who solved it

• Sachin, Gauri, Maneesh and Abhay – Solved it by using the LOOKUP & VLOOKUP
• Harmeet, Priya, Ashish & Chiranjeev – Solved it by usng a combination of IF and other Functions

Read all the answers in the comments here. Thank you so much folks and Malcolm!

 

Read More VLOOKUP Related Tricks

1. How to do a Picture Vlookup
2. Vlookup + 5 Crazy Tricks
3. How to lookup for similar but not matching records
4. Make your VLookup Robust
5. Don’t suffer from VLOOKUPHOBIA – 3 common Vlookup Errors to avoid