Shift Adherence - calculate scheduled shifts vs actual shift worked

[PCRIDE]

Hi, I have 2 time formats in H:MM:SS AM/PM and I need to calculate in one cell the % adhered to the shift.

If someone was scheduled to work 8-5 but actually worked 8:10-5:10, they got their 8 hours in but didn’t work it within the shift.

The logic is

Adherence = minutes within Aherence / Total scheduled min.

So in this case it would be 530/540 =98%. So I need a formula that will look at the two time stamps and calculate this as a percent

Any help will be awesome!

 

[DRSteele]

Do these shifts ever have PM then AM? In other words, could someone start a shift at 8PM and work until 5AM?

Also, if someone worked 8am-5pm he worked 9 hours, not 8.

 

[PCRIDE]

Thanks I meant 9 with an hour lunch that I take out. everyone will work AM to PM shift

 

[DRSteele]

Right. Ok, we'll try this.

Book1
	A	B	C	D	E
1		Scheduled	Laboured	Outside Minutes
2		8:00 AM	8:10 AM	10
3		5:00 PM	5:10 PM	0
4	Minutes	540	540
5				Adherence Rate:	98.15%
Sheet43

Cell Formulas
Range	Formula
D2	=IF(C2C2-B2)*24*60)
D3	=IF(C3>B3,0,(C3-B3)*24*60)
B4	=(B3-B2)*24*60
C4	=(C3-C2)*24*60
E5	=(C4-SUM(D2:D3))/B4

 

[PCRIDE]

Let me try this!

 

[PCRIDE]

Works perfect! Thanks again.

 

[DRSteele]

Super. You're welcome.

 

[PCRIDE]

So I need to also consider time worked before the shift.
If somone logged in early and left early or stayed late

So if someone didn’t work exactly their shift from 8-5, I need to count that.

Here is some info about it
https://www.callcentrehelper.com/how-do-i-calculate-schedule-adherence-113912.htm