How to calculate what hour a time stamped event happened between a start and end time.

[jtedmonson42]

What would be the best way to figure up which hour a timestamp event occured between two times stamps? Say a clock punch for example.
Data set up similar to below. How would I get the 2 in the hour occured?

PunchTime	ShiftStartTime	ShiftEndTime	Hour Occurred
10:00	08:30	18:30	2

 

[Eric W]

Try:

Book4 (version 1).xlsb
	A	B	C	D
1	PunchTime	ShiftStartTime	ShiftEndTime	Hour Occurred
2	10:00	8:30	18:30	2
Sheet6

Cell Formulas
Range	Formula
D2	D2	=ROUNDUP((A2-B2)*24,0)

 

[jtedmonson42]

Apologies I was mistaken on the set up on the data. I forgot about the date being included in the event timestamp, which I need because I'm looking at multiple days. I have a set up like below with a column to pull the event hour using the =hour(a2) formula because I was previously sorting events based on the hour they occurred, but I'm wanting to add the ability to sort by the hour it occurred on each person's shift.

EventTime	EventHour(using the hour() formulat)	ShiftStart	ShiftEnd	HourOccurred
8/21/21 22:30	22	18:30	4:30

 

[Eric W]

Ideally, if you're going to work with times that could span multiple days, you should have the date included in the time in both places, both the EventTIme and the ShiftStart. But as long as a shift doesn't last over 24 hours, this should work:

Book4 (version 1).xlsb
	A	B	C	D	E
1	EventTime	EventHour(using the hour() formulat)	ShiftStart	ShiftEnd	HourOccurred
2	8/21/2021 22:30	22	18:30	4:30	4
Sheet6

Cell Formulas
Range	Formula
E2	E2	=ROUNDUP(((MOD(A2,1)-C2)+(C2>MOD(A2,1)))*24,0)

Note that I did not use the B2 cell, since in so doing we lose the :30 which might be important.

 

[jtedmonson42]

That worked, thank you, and yes shifts are just 10 hours.

 

[Eric W]

Glad I could help. ?