Forum Discussion
how do I group three time ranges into shifts in Excel?
Hello all, anyone know how to group three different time ranges into morning/ evening/ night (or 1/2/3) shifts Excel 2016? thanks!
The problem was two-fold at least.
- 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).
- So that meant we had to extract from that (Date & Time) field the Time only, which I've done in a new column.
- 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.......
11 Replies
- Ranga_KumaraCopper Contributor
Hi, I would appreciate if you can help me why Vlookup is not working here. Kindly help me on this.
- mathetesSilver ContributorPlease start a new thread on your question rather than tacking on to a two year old thread. In the process, please give a bit more information. For example, what are the references in your VLOOKUP formula? I was able to detect that the formula itself is appearing in cell F2 (and copied down), but you use named ranges without telling us where those are.
- Ranga_KumaraCopper Contributor
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
- mathetesSilver Contributor
Create a table with the start time for the shifts, and then use it with VLOOKUP, as in the attached