jbrown021286
Board Regular
- Joined
- Mar 13, 2023
- Messages
- 85
- Office Version
- 365
- Platform
- Windows
hours log.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | O | P | Q | R | S | T | U | V | W | X | Y | ||||
1 | 7/5/2024 | ||||||||||||||||||||||||||
2 | Day | date | notes | Column2 | RO | status | SUM | Daignosis | CODE | JOB | FLAG TIME | ADJUSTMENT | R.O. | Hours | |||||||||||||
3 | Friday | 7/5/2024 | 6215160 | g | 5.0 | aii | 5 | don’t count | Waiting | incorect | 6214863 | 2 | |||||||||||||||
4 | Wednesday | 7/3/2024 | 6215160 | g | 5.0 | aii | 5 | 0 | 0 | 0 | Adjustment | Check List | 6214949 | 0.3 | |||||||||||||
5 | Thursday | 7/4/2024 | 6215271 | g | 3.0 | 3 | Todays total | Balance non verifyed | Balance verifyed | 68.6 | 6214949 | 0.3 | |||||||||||||||
6 | Tuesday | 7/2/2024 | michael | X | 6213019 | 0.7 | pro | propack | 0.7 | 17 | -6.3 | -37.0 | 6214949 | 1 | |||||||||||||
7 | Tuesday | 7/2/2024 | michael | X | 6213019 | 1.4 | pdi | pdi | 1.4 | Total | Not Verifyed | Verifyed | 6214949 | 0.3 | |||||||||||||
8 | Tuesday | 7/2/2024 | michael | X | 6213019 | 3.0 | 3 | 53.7 | 30.7 | 23 | 6214950 | 2.3 | |||||||||||||||
9 | Tuesday | 7/2/2024 | michael | X | 6214863 | 2.0 | camera aim | 2 | 6215031 | 2 | |||||||||||||||||
10 | Tuesday | 7/2/2024 | terrance | 6215187 | g | 6.0 | 6 | 6215035 | 0.5 | ||||||||||||||||||
11 | Wednesday | 7/3/2024 | 6215232 | g | 4.0 | bcm | 4 | Diags Waiting | 6215035 | 0.5 | |||||||||||||||||
12 | Thursday | 7/4/2024 | X | 6215277 | 1.0 | d | Diag | 1 | Writer | Diag | Ro | sum | 6215036 | 1 | |||||||||||||
13 | Thursday | 7/4/2024 | X | 6215281 | 1.5 | 1.5 | 6215040 | 2 | |||||||||||||||||||
14 | Monday | 7/1/2024 | evan | X | 1hgcm564x5a055888 | 1.0 | compressor | d | Diag | 1 | 6215041 | 0.5 | |||||||||||||||
15 | Tuesday | 7/2/2024 | michael | X | 2hgfe2f21rh595175 | 1.4 | pdi | pdi | 1.4 | 6215160 | 5 | ||||||||||||||||
16 | Tuesday | 7/2/2024 | michael | X | 2hgfe2f21rh595176 | 0.7 | pro | propack | 0.7 | 6215160 | 5 | ||||||||||||||||
17 | Tuesday | 7/2/2024 | evan | X | 2hkrm3h50gh511914 | 2.0 | axle seal | 2 | Day | Hours | Average | 6215185 | 1.4 | ||||||||||||||
18 | Monday | 7/1/2024 | richard | X | JH4CL96897C015710 | 1.0 | harness repair | 1 | x | Monday | 5.0 | 10.7 | 6215185 | 0.7 | |||||||||||||
19 | Monday | 7/1/2024 | lindsey | X | jhlre38318c009229 | 1.0 | battery | d | Diag | 1 | x | Tuesday | 17.2 | 6215187 | 6 | ||||||||||||
20 | Monday | 7/1/2024 | lindsey | X | jhlre38318c009229 | 1.0 | discharge hose | d | Diag | 1 | x | Wednesday | 9.0 | 6215213 | 15 | ||||||||||||
21 | Monday | 7/1/2024 | lindsey | X | jhlre38318c009229 | 1.0 | motor | d | Diag | 1 | x | Thursday | 5.5 | 6215213 | 3 | ||||||||||||
22 | Friday | 7/5/2024 | X | 6215235 | 3.0 | 3 | x | Friday | 0.0 | 6215232 | 4 | ||||||||||||||||
23 | Friday | 7/5/2024 | X | 6215235 | 5.0 | 5 | x | Saturday | 0.0 | 6215235 | 3 | ||||||||||||||||
24 | Friday | 7/5/2024 | X | 6215235 | 4.0 | 4 | 6215235 | 4 | |||||||||||||||||||
25 | x | Today | 17 | 6215235 | 4 | ||||||||||||||||||||||
26 | 6215271 | 3 | |||||||||||||||||||||||||
27 | 6215271 | 0.3 | |||||||||||||||||||||||||
28 | 6215281 | 1.5 | |||||||||||||||||||||||||
29 | |||||||||||||||||||||||||||
Log |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =TODAY() |
K3:K29 | K3 | =IF(J3="","",INDEX($AB$3:$AB$91,MATCH(J3,$AA$3:$AA$91,0),1)) |
L3:L29 | L3 | =IF(K3="","",INDEX($AC$3:$AC$91,MATCH(J3,$AA$3:$AA$91,0),1)) |
P4 | P4 | =SUMIF(G:G,"N",H:H)+SUMIF(G:G,"cnf",H:H) |
Q4 | Q4 | =SUMIF(G:G,"w",H:H) |
R4 | R4 | =SUMIF(G:G,"x",H:H) |
U5 | U5 | =SUM(Y:Y)-T5 |
P6 | P6 | =SUMIF($C$3:$C$280,A1,$H3:$H280)-SUMIFS(Table1112933[SUM],Table1112933[date],A1,Table1112933[status],"=n")-SUMIFS(Table1112933[SUM],Table1112933[date],A1,Table1112933[status],"=gn") |
Q6 | Q6 | =P8-AW4 |
R6 | R6 | =R8-AW4 |
P8 | P8 | =SUM(H:H,)-SUMIF(G:G,"N",H:H)-SUMIF(G:G,"nf",H:H)-SUMIF(Table1112933[status],"=GN",Table1112933[SUM]) |
Q8 | Q8 | =SUMIF(G:G,"",H:H)+SUMIF(G:G,"c",H:H) |
R8 | R8 | =SUMIF(G:G,"G",H:H)-Q11 |
C3:C9,C15:C29,C11:C13 | C3 | =IF([@RO]<>"",IF(C3="",TODAY(),C3),"") |
B3:B29 | B3 | =IFERROR(TEXT(WEEKDAY($C3),"Dddd"),"") |
R18 | R18 | =IFERROR(AVERAGEIFS(Q18:Q25,Q18:Q25,">0",O18:O25,"X"),0) |
Q18:Q23 | Q18 | =SUMIFS($H$3:$H$280,$B$3:$B$280,P18,$C$3:$C$280,"<"&A$1)-SUMIFS(Table1112933[SUM],Table1112933[status],"GN",Table1112933[Day],P18)-SUMIFS(Table1112933[SUM],Table1112933[status],"N",Table1112933[Day],P18)-SUMIFS(Table1112933[SUM],Table1112933[status],"W",Table1112933[Day],P18) |
Q25 | Q25 | =SUMIF($C$3:$C$280,A1,$H3:$H280)-SUMIFS(Table1112933[SUM],Table1112933[date],A1,Table1112933[status],"=n")-SUMIFS(Table1112933[SUM],Table1112933[date],A1,Table1112933[status],"=gn")-SUMIFS(Table1112933[SUM],Table1112933[date],A1,Table1112933[status],"=W") |
E3:E26 | E3 | =IF(OR(AND(F3="",G3=""),AND(F3<>"",G3="G")),"",IF(AND(F3<>"",G3="GN"),"",IF(AND(F3<>"",G3=""),"X","***"))) |
E27:E29 | E27 | =IF(OR(AND(D27="",G27=""),AND(D27<>"",G27="G")),"",IF(AND(D27<>"",G27="GN"),"",IF(AND(D27<>"",G27=""),"X","***"))) |
H3:H29 | H3 | =SUMIF(Table1112933[@[FLAG TIME]:[ADJUSTMENT]],"<>",Table1112933[@[FLAG TIME]:[ADJUSTMENT]]) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
X3:Y281 | Expression | =COUNTIF(F:F,X3)>0 | text | NO |
X3:Y281 | Expression | =COUNTIFS(F:F,X3,H:H,Y3)>0 | text | NO |
U13:U35 | Expression | =COUNTIFS(X:X,U13,Y:Y,V13)>0 | text | NO |
V13 | Expression | =COUNTIFS(Y:Y,U13,Z:Z,V13)>0 | text | NO |
G3:G280 | Cell Value | =$P$38 | text | NO |
Q6:R6 | Cell Value | >-0.1 | text | NO |
Q6:R6 | Cell Value | >-0.1 | text | NO |
Q6:R6 | Cell Value | <0 | text | NO |
M22:M52,F30:F32,F34:F88,F91:F280,F3:F26 | Expression | =COUNTIF(X:X,F3)>0 | text | NO |
E3:E280 | Expression | =(E3="x")*(C3=$A$1) | text | NO |
E3:E26,E30:E280 | Expression | =(E3="")*(F3<>"") | text | NO |
E3:E26,E30:E280 | Expression | =(E3="x")*(F3<>"") | text | NO |
G3:G88,G91:G280,I5:I8 | Cell Value | =$P$43 | text | NO |
G3:G88,I5:I8,G91:G280 | Cell Value | =$P$42 | text | NO |
G3:H4,G4:G14,H5:I8,G10:H88,G91:H280,H9 | Cell Value | =$P$41 | text | NO |
H3:H88,H91:H280 | Expression | =" =COUNTIFS(D:D,A1,E:E,J2)>0" | text | NO |
H3:H88,H91:H280 | Expression | =COUNTIFS(X:X,F3,Y:Y,H3)>0 | text | NO |
G3:H4,H5:I8,H9,G10:H88,G91:H280,G4:G14 | Cell Value | =$P$40 | text | NO |
G3:H4,G4:G14,H5:I8,H9,G10:H88,G91:H280 | Cell Value | =$P$39 | text | NO |
G3:H4,G4:G14,H5:I8,H9,G10:H88,G91:H280 | Cell Value | =$P$37 | text | NO |
G3:H4,G4:G14,H5:I8,H9,G10:H88,G91:H280 | Cell Value | =$P$36 | text | NO |
I19 | Cell Value | =$P$42 | text | NO |
I19 | Cell Value | =$P$41 | text | NO |
I19 | Cell Value | =$P$40 | text | NO |
I19 | Cell Value | =$P$39 | text | NO |
I19 | Cell Value | =$P$37 | text | NO |
I19 | Cell Value | =$P$36 | text | NO |
M3:O5 | Expression | =COUNTIFS(XFB:XFB,L3,XFD:XFD,M3)>0 | text | NO |
N6:O7 | Expression | =COUNTIFS(XFC:XFC,M6,#REF!,N6)>0 | text | NO |
E27:E29 | Expression | =(E27="")*(D27<>"") | text | NO |
E27:E29 | Expression | =(E27="x")*(D27<>"") | text | NO |
D29,D33 | Expression | =COUNTIF(X:X,D29)>0 | text | NO |
D29,D33 | Expression | =COUNTIF(XEV:XEV,D29)>0 | text | NO |
i am not sure why my conditioning formulas in columns X and Y. i need the cells in x and y to be highlighted if they have matching cells in f and h. this is a similar function that i have f and h conditionally formatted with. any help would be appreciated