Forum Discussion
Gal1971
Jul 25, 2020Copper Contributor
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!
- Jul 26, 2020
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.......
Gal1971
Jul 26, 2020Copper Contributor
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.
mathetes
Jul 26, 2020Silver Contributor
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.......