Expiration date formula in large spreadsheet; avoiding Data Model

[pam5cashfan]

Hello all. I have a fairly large data set (30,000 lines -will grow to 50,000) which is a set of employee payroll records.

I am hoping to identify a particular type of rule violation - when a worker who has worked both as a "Temp" and as a "Part-Time" has worked hours in violation of the rule that a person is limited to working as a Temp for no more than 14 weeks after the first week in which he/she worked as a Temp.

It is allowable to work for more than 14 weeks if you are a part-time worker.

The output in the "MayBeTempViolation" correctly identifies a violation for Temp workers - but it also incorrectly describes as a "Yes" violation a worker who started off as a Temp, and then ended up as a Part-Time worker more than 14 weeks after the first week worked.

I'd prefer to use a formula that accounts for this employee-category switching. Currently, I pull this data into a Pivot Table and add VLookup to display Violation Type, and then drill down into the data to flag violations that aren't really violations,and then manually delete them. Last month, the formula flagged hundreds of Temp violations that weren't really violations.

I can't use VBA or array formulas. I don't format the data as a Table or use the Data Modeling features (Distinct Count) because my experience is the file becomes large and unstable. And others in my organization with basic Excel skills need to be able to use the spreadsheet.

Thanks!!

	A	B	C	D	E	F
IDNumber	Status	Week-End	Hours Worked	MinPay-End
Part-Time
Temp
Part-Time
Part-Time
Temp
Temp
Part-Time
Temp

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]MayBeTempViolation?[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2344[/TD]

[TD="align: right"]3/9/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]03/09/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2344[/TD]

[TD="align: right"]3/16/2019[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]03/09/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2344[/TD]

[TD="align: right"]3/23/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]03/09/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2344[/TD]

[TD="align: right"]3/30/2019[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]03/09/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7281[/TD]

[TD="align: right"]3/16/2019[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]03/16/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7281[/TD]

[TD="align: right"]3/30/2019[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]03/16/19[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7281[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]03/16/19[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7281[/TD]

[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]03/16/19[/TD]
[TD="align: center"]Yes[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=(IF($C2>($E2+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=(IF($C3>($E3+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A4)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=(IF($C4>($E4+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A5)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=(IF($C5>($E5+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A6)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=(IF($C6>($E6+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A7)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=(IF($C7>($E7+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A8)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=(IF($C8>($E8+14*7),"Yes","No"))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E9[/TH]
[TD="align: left"]=MINIFS(C:C,A:A,A9)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=(IF($C9>($E9+14*7),"Yes","No"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi pam5cashfan,

so your rules are:
-a worker has worked both as a Temp and as a Part-Time
-there cannot be more than 14 weeks between the min & max date of Temp work

what if you change your formulas to something like this:
E2 =MINIFS(C:C,A:A,A2,B:B,"Temp")
F2 =IF(COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2),B2 & " only","both types")
G2 =(AND(IF($C2>($E2+14*7),$B2="Temp",$F2="both types"),"Yes","No"))
That removes a bunch of false positives as you only focus on the Temp category.

Cheers,
Koen

 

[pam5cashfan]

Thank you very much Koen - extremely helpful. I realized that the switching between employee types was not really my problem - rather, I hadn't used MINIFS and MAXIFS to identify hours worked *as a Temp*, which your formula shows. So I used your E2 formula and added a column for a MAXIFS version of that formula. Now I can flag any Temp who worked more than 14 weeks by comparing the two dates.

Thanks again!
Pam