software

Column1 = Orig Hire Date
Column2 = Recent Hire Date
Column 3 = Seniority Date
Column 4 = Benefit Calc Date

I need to come up with a formula that will compare these 4 dates and find
any instance where the dates do not fit into one of the scenerios below:

only 3 scenerios should be possible
1) Orig Hire = Recent Hire = Seniority = Ben Calc
2.) Recent Hire = Seniority, Ben calc a date between Orig Hire and Recent hire
3.) Seniority date after original hire, and orig hire = recent = ben calc
=IF(OR(AND(A2=C2,A2=C2,A2=D2),AND(B2=C2,AND(D2gt;A2, D2lt;B2)),AND(C2gt;A2,AND(A2=B2,A2=D2))),quot;YES!!quot;,quot;NO!! quot;)

Check your quot;betweensquot; for inclusive or not.

--
Kind regards,

Niek Otten

quot;PARquot; gt; wrote in message ...
gt; Column1 = Orig Hire Date
gt; Column2 = Recent Hire Date
gt; Column 3 = Seniority Date
gt; Column 4 = Benefit Calc Date
gt;
gt; I need to come up with a formula that will compare these 4 dates and find
gt; any instance where the dates do not fit into one of the scenerios below:
gt;
gt; only 3 scenerios should be possible
gt; 1) Orig Hire = Recent Hire = Seniority = Ben Calc
gt; 2.) Recent Hire = Seniority, Ben calc a date between Orig Hire and Recent hire
gt; 3.) Seniority date after original hire, and orig hire = recent = ben calc
gt;
gt;
=IF(AND(A2=B2,A2=C2,A2=D2),quot;okquot;,IF(AND(B2=C2,D2gt;A2 ,D2lt;B2),quot;okquot;,IF(AND(A2=B2,A2=D2,C2gt;A2),quot;okquot;,quot;badquot;) ))

Vaya con Dios,
Chuck, CABGx3
quot;PARquot; wrote:

gt; Column1 = Orig Hire Date
gt; Column2 = Recent Hire Date
gt; Column 3 = Seniority Date
gt; Column 4 = Benefit Calc Date
gt;
gt; I need to come up with a formula that will compare these 4 dates and find
gt; any instance where the dates do not fit into one of the scenerios below:
gt;
gt; only 3 scenerios should be possible
gt; 1) Orig Hire = Recent Hire = Seniority = Ben Calc
gt; 2.) Recent Hire = Seniority, Ben calc a date between Orig Hire and Recent hire
gt; 3.) Seniority date after original hire, and orig hire = recent = ben calc
gt;
gt;