Jul 25 2020 10:35 AM
Hello all, anyone know how to group three different time ranges into morning/ evening/ night (or 1/2/3) shifts Excel 2016? thanks!
Jul 25 2020 10:57 AM - edited Jul 25 2020 11:01 AM
Create a table with the start time for the shifts, and then use it with VLOOKUP, as in the attached
Jul 25 2020 01:06 PM
Thanks @mathetes !
copied it to my table, but it is not working - I get the same number 3 for all shifts. I think the problem is that my actual time column is not numbers as in yours - but in Excel date/time format. Guess it matters?
Jul 25 2020 01:51 PM
I think mine were in Excel time format... but either way, Yes, it matters.
Can you post your actual sheet, just so long as it doesn't have real people data on it......There may be something else going on with the VLOOKUP.
Jul 26 2020 01:58 AM
Thanks again, I uploaded a small part. I tried changing your Time column from Custom to Time - still didnt work. I guess the Vlookup doesn't recognize my time, and I didnt succeed in changing it to a mere number.
Jul 26 2020 04:53 AM
Solution
The problem was two-fold at least.
So I think this now works.......
Jul 26 2020 11:30 AM
Jul 26 2020 11:46 AM
You're welcome.
And I have to thank you as well: I learned a bit more about the quirks in date and time formats.
Things aren't always what they appear to be!
Feb 27 2022 10:35 PM
Hi, I would appreciate if you can help me why Vlookup is not working here. Kindly help me on this.
Feb 28 2022 06:52 AM
Feb 28 2022 08:45 PM
@mathetes Thanks for your prompt reply.
Name range - H2:I16
Daily from 9pm to 6am in the following day (H column) the security has to punch in 3 locations. They have -15 to 15 minutes grace period as in J and K column. I have prepared buckets based on the min and max timing.
Patrol time (B column) has the date and timing. So I take the timing and check whether timing is within the bucket through vlookup.
Thanks
Rgds
Mar 01 2022 10:24 AM
Jul 26 2020 04:53 AM
Solution
The problem was two-fold at least.
So I think this now works.......