Averaging + & - times in Text format

C

charliemike9285

New Member
Joined
Jan 21, 2023
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Web
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
TUVWX
2Job DescTotal Shifts# Times left lateAvg Mins left lateColumn1
3RN6670#DIV/0!
4PCA3440#DIV/0!
5UAA610#DIV/0!
reg time format
Cell Formulas
RangeFormula
U3:U5U3=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V3:V5V3=SUMIFS(Table19[Column1],Table19[Job Code Description], [@[Job Desc]],Table19[Time left late (h:mm)], ">0:30")
W3:W5W3=AVERAGEIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]], Table19[Time left late (h:mm)], "<>0")

Employee Clocking Times.xlsx
AFGIJKNOR
7Column1Job Code DescriptionShiftShift End TimeClock DateClock-In TimeShift End DateClock-Out TimeTime left late (h:mm)
311PCAAM07:15 PM05/09/202406:37 AM 07:30 PM0:15
321PCAAM07:15 PM05/09/202406:50 AM 07:24 PM0:09
331PCAAM07:15 PM05/09/202406:45 AM 07:23 PM0:08
341PCAAM07:15 PM05/09/202406:35 AM 07:21 PM0:06
351PCAAM07:15 PM05/09/202406:38 AM 07:09 PM-0:06
361PCAAM07:15 PM05/09/202406:44 AM 07:08 PM-0:07
371PCAPM07:15 AM05/08/202406:48 PM5/9/202407:37 AM0:22
381PCAAM07:15 PM05/08/202406:49 AM 07:28 PM0:13
391PCAAM07:15 PM05/08/202406:45 AM 07:27 PM0:12
401PCAAM07:15 PM05/08/202406:40 AM 07:17 PM0:02
411PCAAM07:15 PM05/08/202406:41 AM 07:15 PM0:00
421PCAPM07:15 AM05/08/202406:38 PM5/9/202407:15 AM0:00
431PCAPM07:15 AM05/08/202405:57 PM5/9/202407:12 AM-0:03
441PCAAM07:15 PM05/08/202406:35 AM 07:11 PM-0:04
451PCAPM07:15 AM05/08/202406:43 PM5/9/202407:09 AM-0:06
461PCAAM07:15 PM05/07/202406:54 AM 07:41 PM0:26
471PCAAM07:15 PM05/07/202406:40 AM 07:30 PM0:15
481PCAPM07:15 AM05/07/202406:46 PM5/8/202407:23 AM0:08
491PCAAM07:15 PM05/07/202406:38 AM 07:17 PM0:02
501PCAAM07:15 PM05/07/202406:52 AM 07:17 PM0:02
511PCAPM07:15 AM05/07/202406:36 PM5/8/202407:13 AM-0:02
521PCAPM07:15 AM05/07/202406:43 PM5/8/202407:12 AM-0:03
531PCAPM07:15 AM05/07/202406:45 PM5/8/202407:02 AM-0:13
541PCAPM07:15 AM05/07/202406:55 PM5/8/202407:09 AM-0:06
551PCAAM07:15 PM05/07/202406:43 AM 07:08 PM-0:07
561PCAAM07:15 PM05/07/202406:51 AM 07:08 PM-0:07
571PCAPM07:15 AM05/06/202406:36 PM5/7/202407:50 AM0:35
581PCAAM07:15 PM05/06/202406:47 AM 07:39 PM0:24
591PCAAM07:15 PM05/06/202406:44 AM 07:34 PM0:19
601PCAPM07:15 AM05/06/202406:43 PM5/7/202407:28 AM0:13
611PCAPM07:15 AM05/06/202406:33 PM5/7/202407:27 AM0:12
621PCAPM07:15 AM05/06/202407:09 PM5/7/202407:20 AM0:05
631PCAAM07:15 PM05/06/202406:38 AM 07:19 PM0:04
641PCAAM07:15 PM05/06/202406:39 AM 07:19 PM0:04
651PCAPM07:15 AM05/06/202406:36 PM5/7/202407:14 AM-0:01
reg time format
Cell Formulas
RangeFormula
I31:I65I31=TEXT([@[Shift End Time (Mil)]], "hh:mm AM/PM")
K31:K65K31=TEXT([@[In Time3]], "hh:mm AM/PM")
N31:N65N31=IF([@Shift]="PM",([@[Clock Date]]+1),"")
O31:O65O31=TEXT([@[Out Time3]], "hh:mm AM/PM")
R31:R65R31=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"))
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
hagia_sofia said:
Would adjusting the last part from ">0:30" to ">"&"0:30" help your cause?
Click to expand...
Just tried it and it's still showing as 0.

Employee Clocking Times.xlsx
TUVWX
2Job DescTotal Shifts# Times left lateAvg Mins left latesum
3RN6670#DIV/0!0:00
4PCA3440#DIV/0!0:00
5UAA610#DIV/0!0:00
reg time format
Cell Formulas
RangeFormula
U3:U5U3=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V3:V5V3=SUMIFS(Table19[Column1],Table19[Job Code Description], [@[Job Desc]],Table19[Time left late (h:mm)], ">"&"0:30")
W3:W5W3=AVERAGEIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]], Table19[Time left late (h:mm)], "<>0")
X3:X5X3=SUMIFS(Table19[Time left late (h:mm)], Table19[Job Code Description], [@[Job Desc]])
 
Upvote 0
How about this then (adjust according to your actual arrays):

Excel Formula: 
=SUM(--(FILTER(R2:R36,(A2:A36=1)*(F2:F36="PCA")*(ISNUMBER(R2:R36)))>(1/24/2)))
 
Upvote 0
See if this looks right:

20240526 SumIfs AverageIfs Negative Time charliemike9285.xlsx
TUVW
1Job DescTotal Shifts# Times left lateAvg Mins left late
2RN0000:00
3PCA35100:05
4UAA0000:00
Data
Cell Formulas
RangeFormula
U2:U4U2=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V2:V4V2=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:W4W2=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))
 
Upvote 0
Alex Blakenburg said:
See if this looks right:

20240526 SumIfs AverageIfs Negative Time charliemike9285.xlsx
TUVW
1Job DescTotal Shifts# Times left lateAvg Mins left late
2RN0000:00
3PCA35100:05
4UAA0000:00
Data
Cell Formulas
RangeFormula
U2:U4U2=COUNTIFS(Table19[Job Code Description], [@[Job Desc]])
V2:V4V2=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:W4W2=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))
Click to expand...


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
TUVW
2Job DescTotal Shifts# Times left lateAvg Mins left late
3RN6674640.04
4PCA344320.01
5UAA6120.00
reg time format (2)
Cell Formulas
RangeFormula
U3:U5U3=COUNTIFS(Table1914[Job Code Description], [@[Job Desc]])
V3:V5V3=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:W5W3=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))
 
Upvote 0
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
TUVWX
2Job DescTotal Shifts# Times left >30mins lateAvg Mins left late% times left >30mins late
3RN6674640:5370%
4PCA344320:099%
5UAA612############3%
reg time format (2)
Cell Formulas
RangeFormula
U3:U5U3=COUNTIFS(Table1914[Job Code Description], [@[Job Desc]])
V3:V5V3=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:W5W3=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:X5X3=[@['# Times left >30mins late]]/[@[Total Shifts]]

Employee Clocking Times.xlsx
AFGIJKNOR
7Column1Job Code DescriptionShiftShift End TimeClock DateClock-In TimeShift End DateClock-Out TimeTime left late (h:mm)
81UAAEVE11:15 PM04/20/202402:44 PM 11:16 PM0:01
91UAAEVE11:15 PM04/05/202402:35 PM 11:15 PM0:00
101UAA1st03:15 PM04/18/202406:44 AM 03:12 PM-0:03
111UAAEVE11:15 PM04/18/202402:44 PM 11:12 PM-0:03
121UAAEVE11:15 PM05/08/202402:39 PM 11:01 PM-0:14
131UAAEVE11:15 PM04/10/202402:42 PM 11:00 PM-0:15
141UAAEVE11:15 PM04/15/202402:42 PM 10:58 PM-0:17
151UAAEVE11:15 PM04/04/202402:44 PM 10:55 PM-0:20
161UAAEVE11:15 PM04/25/202402:38 PM 10:55 PM-0:20
171UAAEVE11:15 PM05/02/202402:39 PM 10:55 PM-0:20
181UAA1st03:15 PM04/11/202406:38 AM 03:12 PM-0:03
191UAAEVE11:15 PM04/11/202402:44 PM 10:52 PM-0:23
201UAA1st03:15 PM04/12/202406:43 AM 03:12 PM-0:03
211UAAEVE11:15 PM04/12/202402:44 PM 10:45 PM-0:30
221UAAPM07:15 AM04/23/202410:41 PM4/24/202407:10 AM-0:05
231UAAPM07:15 AM05/06/202410:38 PM5/7/202407:10 AM-0:05
241UAAPM07:15 AM04/25/202410:39 PM4/26/202407:09 AM-0:06
251UAAPM07:15 AM04/21/202410:38 PM4/22/202407:08 AM-0:07
261UAAPM07:15 AM04/24/202410:38 PM4/25/202407:08 AM-0:07
271UAAPM07:15 AM04/26/202410:38 PM4/27/202407:08 AM-0:07
281UAAPM07:15 AM05/03/202410:38 PM5/4/202407:08 AM-0:07
291UAAPM07:15 AM05/07/202410:38 PM5/8/202407:08 AM-0:07
301UAA1st03:15 PM04/10/202407:06 AM 04:09 PM0:54
311UAA1st03:15 PM04/24/202406:45 AM 03:53 PM0:38
321UAA1st03:15 PM05/08/202406:48 AM 03:38 PM0:23
331UAA1st03:15 PM04/15/202406:41 AM 03:33 PM0:18
341UAA1st03:15 PM04/13/202406:45 AM 03:32 PM0:17
351UAA1st03:15 PM05/01/202406:52 AM 03:26 PM0:11
361UAA1st03:15 PM04/02/202406:48 AM 03:25 PM0:10
371UAA1st03:15 PM04/06/202406:39 AM 03:23 PM0:08
381UAA1st03:15 PM04/09/202406:30 AM 03:21 PM0:06
391UAA1st03:15 PM05/09/202406:38 AM 03:21 PM0:06
401UAA1st03:15 PM04/22/202406:42 AM 03:20 PM0:05
411UAA1st03:15 PM04/07/202406:38 AM 03:18 PM0:03
421UAA1st03:15 PM05/03/202406:45 AM 03:18 PM0:03
431UAA1st03:15 PM04/01/202409:00 AM 03:16 PM0:01
441UAA1st03:15 PM04/14/202407:30 AM 03:15 PM0:00
451UAA1st03:15 PM04/16/202406:36 AM 03:15 PM0:00
461UAA1st03:15 PM04/30/202406:38 AM 03:15 PM0:00
471UAA1st03:15 PM04/03/202406:31 AM 03:14 PM-0:01
481UAA1st03:15 PM04/25/202406:45 AM 03:13 PM-0:02
491UAA1st03:15 PM04/21/202406:30 AM 03:12 PM-0:03
501UAA1st03:15 PM04/26/202407:02 AM 03:12 PM-0:03
511UAA1st03:15 PM04/28/202406:42 AM 03:11 PM-0:04
521UAA1st03:15 PM04/17/202406:36 AM 03:09 PM-0:06
531UAA1st03:15 PM05/02/202406:45 AM 03:09 PM-0:06
541UAA1st03:15 PM04/27/202406:43 AM 03:09 PM-0:06
reg time format (2)
Cell Formulas
RangeFormula
I8:I54I8=TEXT([@[Shift End Time (Mil)]], "hh:mm AM/PM")
K8:K54K8=TEXT([@[In Time3]], "hh:mm AM/PM")
N8:N54N8=IF([@Shift]="PM",([@[Clock Date]]+1),"")
O8:O54O8=TEXT([@[Out Time3]], "hh:mm AM/PM")
R8:R54R8=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"))
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
217
XLfriend777
XLfriend777
JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
174
awoohaw
awoohaw
C
  • Question
Subtotals and counta I think
Replies
1
Views
594
t0ny84
T
K
  • Question
IF AND OR
Replies
1
Views
223
kashifshahzad
K
JenniferMurphy
  • Question
Can Excel emulate a database?
Replies
3
Views
323
Legacy 143009
L

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top