Grouping by times

[Ryan3376]

Greetings,

I have a list of events along with the times they occurred. I want to group the times into shifts with the display being 1 for the first shift (midnight to 7AM), 2 for the second shift (7AM to 3PM), and 3 for the third shift (3PM to midnight). I then plan on putting my data into a pivot table so that I can easily count the events with the rows displaying as a 1,2,3 for each shift.

Any help would be much appreciated.

[Sam Capricci]

here is one way (adjust to your data)...
=LOOKUP(HOUR(A2),{0,7,15},{"1","2","3"})

[Ryan3376]

Thanks. So if my times are displayed starting in F2 and I want to set the ranges as 0:00:01 to 7:00:00 as shift 1, 7:00:01 to 3:00:00 as shift 2, and 3:00:01 to 11:59:59 as shift 3, what would that look like?

[Ryan3376]

Thanks. So if my times are displayed starting in F2 and I want to set the ranges as 0:00:01 to 7:00:00 as shift 1, 7:00:01 to 3:00:00 as shift 2, and 3:00:01 to 11:59:59 as shift 3, what would that look like?

[Sam Capricci]

like this...
=LOOKUP(HOUR(F2),{0,7,15},{"1","2","3"})
it should work based on what you wrote (it does in my test file).

or if you want them numeric instead of text then this...
=LOOKUP(HOUR(F2),{0,7,15},{1,2,3})

[Ryan3376]

That seems to work. Thanks!

[Sam Capricci]

great, glad it worked for you and thank you for the rep!