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"))

 

[hagia_sofia]

Would adjusting the last part from ">0:30" to ">"&"0:30" help your cause?

 

[charliemike9285]

Would adjusting the last part from ">0:30" to ">"&"0:30" help your cause?

Just tried it and it's still showing as 0.

Employee Clocking Times.xlsx
	T	U	V	W	X
2	Job Desc	Total Shifts	# Times left late	Avg Mins left late	sum
3	RN	667	0	#DIV/0!	0:00
4	PCA	344	0	#DIV/0!	0:00
5	UAA	61	0	#DIV/0!	0:00
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")
X3:X5	X3	=SUMIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]])

 

[hagia_sofia]

How about this then (adjust according to your actual arrays):

=SUM(--(FILTER(R2:R36,(A2:A36=1)*(F2:F36="PCA")*(ISNUMBER(R2:R36)))>(1/24/2)))

 

[Alex Blakenburg]

See if this looks right:

20240526 SumIfs AverageIfs Negative Time charliemike9285.xlsx
	T	U	V	W
1	Job Desc	Total Shifts	# Times left late	Avg Mins left late
2	RN	0	0	00:00
3	PCA	35	1	00:05
4	UAA	0	0	00:00
Data

Cell Formulas
Range	Formula
U2:U4	U2	=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V2:V4	V2	=LET(strTime,Table19[Time left late (h:mm)], valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)), SUM((valTime > TIME(0,30,0))*(Table19[Job Code Description]=[@[Job Desc]])*Table19[Column1]))
W2:W4	W2	=LET(strTime,Table19[Time left late (h:mm)], valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)), sumTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])*valTime), countTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])), IFERROR(sumTime/countTime,0))

 

[charliemike9285]

See if this looks right:

20240526 SumIfs AverageIfs Negative Time charliemike9285.xlsx
	T	U	V	W
1	Job Desc	Total Shifts	# Times left late	Avg Mins left late
2	RN	0	0	00:00
3	PCA	35	1	00:05
4	UAA	0	0	00:00
Data

Cell Formulas
Range	Formula
U2:U4	U2	=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V2:V4	V2	=LET(strTime,Table19[Time left late (h:mm)], valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)), SUM((valTime > TIME(0,30,0))*(Table19[Job Code Description]=[@[Job Desc]])*Table19[Column1]))
W2:W4	W2	=LET(strTime,Table19[Time left late (h:mm)], valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)), sumTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])*valTime), countTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])), IFERROR(sumTime/countTime,0))

So I'll have to manually count when i have time later today to see if it is correct, but it seems like it's working! Thank you!!
The only thing is the Avg Mins left Late looks different when I plug it in than what yours looks like and I'm not sure why?

Also, if I wanted to change the time from 30mins to 15mins or even any minutes past the clock out time, where in your formula would I be able to change that?

Employee Clocking Times.xlsx
	T	U	V	W
2	Job Desc	Total Shifts	# Times left late	Avg Mins left late
3	RN	667	464	0.04
4	PCA	344	32	0.01
5	UAA	61	2	0.00
reg time format (2)

Cell Formulas
Range	Formula
U3:U5	U3	=COUNTIFS(Table1914[Job Code Description], [@[Job Desc]])
V3:V5	V3	=LET(strTime,Table19[Time left late (h:mm)],valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)),SUM((valTime > TIME(0,30,0))*(Table19[Job Code Description]=[@[Job Desc]])*Table19[Column1]))
W3:W5	W3	=LET(strTime,Table19[Time left late (h:mm)],valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)),sumTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])*valTime),countTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])),IFERROR(sumTime/countTime,0))

 

[charliemike9285]

So I figured out part of the problem with how it's showing up on my end and it was the formatting (i had those cells formatted as Number and not time). I corrected them but the UAA Avg mins left late is is showing as "#########" which is most likely because they actually left early and not late.
I know the Abs function can help show the negative values in excel, I just don't know I'm out of my depth here and am not sure where to add it in to have it show up in the cell??

Same thing with if I wanted to change the time from greater than 30mins to greater than 15mins (or even any minutes past the clock out time), where in your formula would I be able to change that?

Employee Clocking Times.xlsx
	T	U	V	W	X
2	Job Desc	Total Shifts	# Times left >30mins late	Avg Mins left late	% times left >30mins late
3	RN	667	464	0:53	70%
4	PCA	344	32	0:09	9%
5	UAA	61	2	############	3%
reg time format (2)

Cell Formulas
Range	Formula
U3:U5	U3	=COUNTIFS(Table1914[Job Code Description], [@[Job Desc]])
V3:V5	V3	=LET(strTime,Table19[Time left late (h:mm)],valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)),SUM((valTime > TIME(0,30,0))*(Table19[Job Code Description]=[@[Job Desc]])*Table19[Column1]))
W3:W5	W3	=LET(strTime,Table19[Time left late (h:mm)],valTime,IF(LEFT(strTime,1)="-", -1*TIMEVALUE( RIGHT( strTime,LEN(strTime)-1 ) ), TIMEVALUE(strTime)),sumTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])*valTime),countTime,SUM((valTime <> TIME(0,0,0))*(Table19[Job Code Description]=[@[Job Desc]])),IFERROR(sumTime/countTime,0))
X3:X5	X3	=[@['# Times left >30mins late]]/[@[Total Shifts]]

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)
8	1					UAA	EVE		11:15 PM	04/20/2024	02:44 PM				11:16 PM			0:01
9	1					UAA	EVE		11:15 PM	04/05/2024	02:35 PM				11:15 PM			0:00
10	1					UAA	1st		03:15 PM	04/18/2024	06:44 AM				03:12 PM			-0:03
11	1					UAA	EVE		11:15 PM	04/18/2024	02:44 PM				11:12 PM			-0:03
12	1					UAA	EVE		11:15 PM	05/08/2024	02:39 PM				11:01 PM			-0:14
13	1					UAA	EVE		11:15 PM	04/10/2024	02:42 PM				11:00 PM			-0:15
14	1					UAA	EVE		11:15 PM	04/15/2024	02:42 PM				10:58 PM			-0:17
15	1					UAA	EVE		11:15 PM	04/04/2024	02:44 PM				10:55 PM			-0:20
16	1					UAA	EVE		11:15 PM	04/25/2024	02:38 PM				10:55 PM			-0:20
17	1					UAA	EVE		11:15 PM	05/02/2024	02:39 PM				10:55 PM			-0:20
18	1					UAA	1st		03:15 PM	04/11/2024	06:38 AM				03:12 PM			-0:03
19	1					UAA	EVE		11:15 PM	04/11/2024	02:44 PM				10:52 PM			-0:23
20	1					UAA	1st		03:15 PM	04/12/2024	06:43 AM				03:12 PM			-0:03
21	1					UAA	EVE		11:15 PM	04/12/2024	02:44 PM				10:45 PM			-0:30
22	1					UAA	PM		07:15 AM	04/23/2024	10:41 PM			4/24/2024	07:10 AM			-0:05
23	1					UAA	PM		07:15 AM	05/06/2024	10:38 PM			5/7/2024	07:10 AM			-0:05
24	1					UAA	PM		07:15 AM	04/25/2024	10:39 PM			4/26/2024	07:09 AM			-0:06
25	1					UAA	PM		07:15 AM	04/21/2024	10:38 PM			4/22/2024	07:08 AM			-0:07
26	1					UAA	PM		07:15 AM	04/24/2024	10:38 PM			4/25/2024	07:08 AM			-0:07
27	1					UAA	PM		07:15 AM	04/26/2024	10:38 PM			4/27/2024	07:08 AM			-0:07
28	1					UAA	PM		07:15 AM	05/03/2024	10:38 PM			5/4/2024	07:08 AM			-0:07
29	1					UAA	PM		07:15 AM	05/07/2024	10:38 PM			5/8/2024	07:08 AM			-0:07
30	1					UAA	1st		03:15 PM	04/10/2024	07:06 AM				04:09 PM			0:54
31	1					UAA	1st		03:15 PM	04/24/2024	06:45 AM				03:53 PM			0:38
32	1					UAA	1st		03:15 PM	05/08/2024	06:48 AM				03:38 PM			0:23
33	1					UAA	1st		03:15 PM	04/15/2024	06:41 AM				03:33 PM			0:18
34	1					UAA	1st		03:15 PM	04/13/2024	06:45 AM				03:32 PM			0:17
35	1					UAA	1st		03:15 PM	05/01/2024	06:52 AM				03:26 PM			0:11
36	1					UAA	1st		03:15 PM	04/02/2024	06:48 AM				03:25 PM			0:10
37	1					UAA	1st		03:15 PM	04/06/2024	06:39 AM				03:23 PM			0:08
38	1					UAA	1st		03:15 PM	04/09/2024	06:30 AM				03:21 PM			0:06
39	1					UAA	1st		03:15 PM	05/09/2024	06:38 AM				03:21 PM			0:06
40	1					UAA	1st		03:15 PM	04/22/2024	06:42 AM				03:20 PM			0:05
41	1					UAA	1st		03:15 PM	04/07/2024	06:38 AM				03:18 PM			0:03
42	1					UAA	1st		03:15 PM	05/03/2024	06:45 AM				03:18 PM			0:03
43	1					UAA	1st		03:15 PM	04/01/2024	09:00 AM				03:16 PM			0:01
44	1					UAA	1st		03:15 PM	04/14/2024	07:30 AM				03:15 PM			0:00
45	1					UAA	1st		03:15 PM	04/16/2024	06:36 AM				03:15 PM			0:00
46	1					UAA	1st		03:15 PM	04/30/2024	06:38 AM				03:15 PM			0:00
47	1					UAA	1st		03:15 PM	04/03/2024	06:31 AM				03:14 PM			-0:01
48	1					UAA	1st		03:15 PM	04/25/2024	06:45 AM				03:13 PM			-0:02
49	1					UAA	1st		03:15 PM	04/21/2024	06:30 AM				03:12 PM			-0:03
50	1					UAA	1st		03:15 PM	04/26/2024	07:02 AM				03:12 PM			-0:03
51	1					UAA	1st		03:15 PM	04/28/2024	06:42 AM				03:11 PM			-0:04
52	1					UAA	1st		03:15 PM	04/17/2024	06:36 AM				03:09 PM			-0:06
53	1					UAA	1st		03:15 PM	05/02/2024	06:45 AM				03:09 PM			-0:06
54	1					UAA	1st		03:15 PM	04/27/2024	06:43 AM				03:09 PM			-0:06
reg time format (2)

Cell Formulas
Range	Formula
I8:I54	I8	=TEXT([@[Shift End Time (Mil)]], "hh:mm AM/PM")
K8:K54	K8	=TEXT([@[In Time3]], "hh:mm AM/PM")
N8:N54	N8	=IF([@Shift]="PM",([@[Clock Date]]+1),"")
O8:O54	O8	=TEXT([@[Out Time3]], "hh:mm AM/PM")
R8:R54	R8	=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]

I don't have access to my computer at the moment.
For changing the 30 min to 15 min just change that in the Sum formula currently
TIME(0,30,0)
You didn't indicate it factored into your average calculation.

If you are saying a negative average is possible then we will need than if statement to convert the negative result to a text value. I can do that in an hour or so when I am back at my computer.

 

[Alex Blakenburg]

You will need to do a find and replace on the Table name to match your table name:
In V3
(change the TIME(0,30,0) to TIME(0,15,0) if you need 15 mins)

=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)),
             SUM((valTime > TIME(0,30,0))*(Table1914[Job Code Description]=[@[Job Desc]])*Table1914[Column1]))

in W3 try

=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)),
              sumTime,SUM((valTime <> TIME(0,0,0))*(Table1914[Job Code Description]=[@[Job Desc]])*valTime),
              countTime,SUM((valTime <> TIME(0,0,0))*(Table1914[Job Code Description]=[@[Job Desc]])),
              avgTime,IFERROR(sumTime/countTime,0),
               IF(avgTime<0,"-"&TEXT(ABS(avgTime),"hh:mm"),avgTime))

 

[charliemike9285]

I don't have access to my computer at the moment.
For changing the 30 min to 15 min just change that in the Sum formula currently
TIME(0,30,0)
You didn't indicate it factored into your average calculation.

If you are saying a negative average is possible then we will need than if statement to convert the negative result to a text value. I can do that in an hour or so when I am back at my computer.

@Alex Blakenburg You're right! I did not account for the time in my average calculation! How would I adjust the formula to account for it in the average calculation?

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.

=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)),sumTime,SUM((valTime <> TIME(0,30,0))*(Table1914[Job Code Description]=[@[Job Desc]])*valTime), countTime,SUM((valTime <> TIME(0,30,0))*(Table1914[Job Code Description]=[@[Job Desc]])), avgTime,IFERROR(sumTime/countTime,0), IF(avgTime<0,"-"&TEXT(ABS(avgTime),"hh:mm"),avgTime))