SOLVED

how do I group three time ranges into shifts in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-1545793%22%20slang%3D%22en-US%22%3Ehow%20do%20I%20group%20three%20time%20ranges%20into%20shifts%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545793%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20anyone%20know%20how%20to%20group%20three%20different%20time%20ranges%20into%20morning%2F%20evening%2F%20night%20(or%201%2F2%2F3)%20shifts%20Excel%202016%3F%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1545793%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545811%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20I%20group%20three%20time%20ranges%20into%20shifts%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F739864%22%20target%3D%22_blank%22%3E%40Gal1971%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20table%20with%20the%20start%20time%20for%20the%20shifts%2C%20and%20then%20use%20it%20with%20VLOOKUP%2C%20as%20in%20the%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1545939%22%20slang%3D%22en-US%22%3ERe%3A%20how%20do%20I%20group%20three%20time%20ranges%20into%20shifts%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1545939%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3Ecopied%20it%20to%20my%20table%2C%20but%20it%20is%20not%20working%20-%20I%20get%20the%20same%20number%203%20for%20all%20shifts.%20I%20think%20the%20problem%20is%20that%20my%20actual%20time%20column%20is%20not%20numbers%20as%20in%20yours%20-%20but%20in%20Excel%20date%2Ftime%20format.%20Guess%20it%20matters%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello all, anyone know how to group three different time ranges into morning/ evening/ night (or 1/2/3) shifts Excel 2016? thanks!

7 Replies

@Gal1971 

 

Create a table with the start time for the shifts, and then use it with VLOOKUP, as in the attached

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?

@Gal1971 

 

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.

@mathetes 

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. 

Best Response confirmed by Gal1971 (New Contributor)
Solution

@Gal1971 

 

The problem was two-fold at least.

  1. The column in which you were displaying "Visit Time" actually contained DATE and TIME (I've changed the format only in the attached to make that visible; I did not change the values you had in those cells).
  2. So that meant we had to extract from that (Date & Time) field the Time only, which I've done in a new column.
  3. And I then formatted both that column and the table's column to read with the standard Time format. I also put in midnight as part of the night shift, for the VLOOKUP fallback when the time of the visit is in the wee-small-hours of the morning.

 

So I think this now works.......

@mathetes 

WOW! thanks a million!

Perfect!

Gal

@Gal1971 

 

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!