Averaging + & - times in Text format

[charliemike9285]

I have 3 different groups of employees (RN, PCA, UAA). For the simplicity of this post I've only included a few of the results for PCAs.
I can't figure out what I'm doing wrong in the yellow cells - formulas to count the # of times one of those categories clocked out > 30 mins (0:30) late and the average of the times they left late.
I think it has to do with the fact that the formula I had to use in column R to be able to get negative times to show (if they clocked out early) is in text format because of the formula TEXT(ABS()).
*I cannot use the 1904 time in options though because "options" is locked due to it being a company computer/program*

Current formula for "# of times left late":
=SUMIFS(Table19[Column1],Table19[Job Code Description], [@[Job Desc]],Table19[Time left late (h:mm)], ">0:30")

Current formula for "Avg of time left late (h:mm)":
=AVERAGEIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]], Table19[Time left late (h:mm)], "<>0")

I'm hoping it's just a simple error with how I'm writing the condition of >30mins.....any help would be greatly appreciated, thanks!

Employee Clocking Times.xlsx
	T	U	V	W	X
2	Job Desc	Total Shifts	# Times left late	Avg Mins left late	Column1
3	RN	667	0	#DIV/0!
4	PCA	344	0	#DIV/0!
5	UAA	61	0	#DIV/0!
reg time format

Cell Formulas
Range	Formula
U3:U5	U3	=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V3:V5	V3	=SUMIFS(Table19[Column1],Table19[Job Code Description], [@[Job Desc]],Table19[Time left late (h:mm)], ">0:30")
W3:W5	W3	=AVERAGEIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]], Table19[Time left late (h:mm)], "<>0")

Employee Clocking Times.xlsx
	A					F	G		I	J	K			N	O			R
7	Column1					Job Code Description	Shift		Shift End Time	Clock Date	Clock-In Time			Shift End Date	Clock-Out Time			Time left late (h:mm)
31	1					PCA	AM		07:15 PM	05/09/2024	06:37 AM				07:30 PM			0:15
32	1					PCA	AM		07:15 PM	05/09/2024	06:50 AM				07:24 PM			0:09
33	1					PCA	AM		07:15 PM	05/09/2024	06:45 AM				07:23 PM			0:08
34	1					PCA	AM		07:15 PM	05/09/2024	06:35 AM				07:21 PM			0:06
35	1					PCA	AM		07:15 PM	05/09/2024	06:38 AM				07:09 PM			-0:06
36	1					PCA	AM		07:15 PM	05/09/2024	06:44 AM				07:08 PM			-0:07
37	1					PCA	PM		07:15 AM	05/08/2024	06:48 PM			5/9/2024	07:37 AM			0:22
38	1					PCA	AM		07:15 PM	05/08/2024	06:49 AM				07:28 PM			0:13
39	1					PCA	AM		07:15 PM	05/08/2024	06:45 AM				07:27 PM			0:12
40	1					PCA	AM		07:15 PM	05/08/2024	06:40 AM				07:17 PM			0:02
41	1					PCA	AM		07:15 PM	05/08/2024	06:41 AM				07:15 PM			0:00
42	1					PCA	PM		07:15 AM	05/08/2024	06:38 PM			5/9/2024	07:15 AM			0:00
43	1					PCA	PM		07:15 AM	05/08/2024	05:57 PM			5/9/2024	07:12 AM			-0:03
44	1					PCA	AM		07:15 PM	05/08/2024	06:35 AM				07:11 PM			-0:04
45	1					PCA	PM		07:15 AM	05/08/2024	06:43 PM			5/9/2024	07:09 AM			-0:06
46	1					PCA	AM		07:15 PM	05/07/2024	06:54 AM				07:41 PM			0:26
47	1					PCA	AM		07:15 PM	05/07/2024	06:40 AM				07:30 PM			0:15
48	1					PCA	PM		07:15 AM	05/07/2024	06:46 PM			5/8/2024	07:23 AM			0:08
49	1					PCA	AM		07:15 PM	05/07/2024	06:38 AM				07:17 PM			0:02
50	1					PCA	AM		07:15 PM	05/07/2024	06:52 AM				07:17 PM			0:02
51	1					PCA	PM		07:15 AM	05/07/2024	06:36 PM			5/8/2024	07:13 AM			-0:02
52	1					PCA	PM		07:15 AM	05/07/2024	06:43 PM			5/8/2024	07:12 AM			-0:03
53	1					PCA	PM		07:15 AM	05/07/2024	06:45 PM			5/8/2024	07:02 AM			-0:13
54	1					PCA	PM		07:15 AM	05/07/2024	06:55 PM			5/8/2024	07:09 AM			-0:06
55	1					PCA	AM		07:15 PM	05/07/2024	06:43 AM				07:08 PM			-0:07
56	1					PCA	AM		07:15 PM	05/07/2024	06:51 AM				07:08 PM			-0:07
57	1					PCA	PM		07:15 AM	05/06/2024	06:36 PM			5/7/2024	07:50 AM			0:35
58	1					PCA	AM		07:15 PM	05/06/2024	06:47 AM				07:39 PM			0:24
59	1					PCA	AM		07:15 PM	05/06/2024	06:44 AM				07:34 PM			0:19
60	1					PCA	PM		07:15 AM	05/06/2024	06:43 PM			5/7/2024	07:28 AM			0:13
61	1					PCA	PM		07:15 AM	05/06/2024	06:33 PM			5/7/2024	07:27 AM			0:12
62	1					PCA	PM		07:15 AM	05/06/2024	07:09 PM			5/7/2024	07:20 AM			0:05
63	1					PCA	AM		07:15 PM	05/06/2024	06:38 AM				07:19 PM			0:04
64	1					PCA	AM		07:15 PM	05/06/2024	06:39 AM				07:19 PM			0:04
65	1					PCA	PM		07:15 AM	05/06/2024	06:36 PM			5/7/2024	07:14 AM			-0:01
reg time format

Cell Formulas
Range	Formula
I31:I65	I31	=TEXT([@[Shift End Time (Mil)]], "hh:mm AM/PM")
K31:K65	K31	=TEXT([@[In Time3]], "hh:mm AM/PM")
N31:N65	N31	=IF([@Shift]="PM",([@[Clock Date]]+1),"")
O31:O65	O31	=TEXT([@[Out Time3]], "hh:mm AM/PM")
R31:R65	R31	=IF([@[Clock-Out Time]]-[@[Shift End Time]]>=0, TEXT([@[Clock-Out Time]]-[@[Shift End Time]], "h:mm"),TEXT(ABS([@[Clock-Out Time]]-[@[Shift End Time]]),"-h:mm"))

 

[Alex Blakenburg]

In W3 I made sure the Time(0,0,0) was changed to Time(0,30,0) however, it did not change the times...so I am unsure how to fix that.

You previously had a requirement to Exclude time = 0 (<> in the formula). Since > 30 mins already excludes 0 you can replace that part with > 30 mins.
Try this:

=LET(strTime,TEXT(Table1914[Time left late (h:mm)],"hh:mm"),
            valTime,IF(LEFT(strTime,1)="-",
                                     -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ),
                                     TIMEVALUE(strTime)),
             toleranceTime, TIME(0,30,0),
              sumTime,SUM((valTime > toleranceTime)*(Table1914[Job Code Description]=[@[Job Desc]])*valTime),
              countTime,SUM((valTime > toleranceTime)*(Table1914[Job Code Description]=[@[Job Desc]])),
              avgTime,IFERROR(sumTime/countTime,0),
               IF(avgTime<0,"-"&TEXT(ABS(avgTime),"hh:mm"),avgTime))