Date Comparison Calculation

[Guest]

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

 

[Niek Otten]

=IF(OR(AND(A2=C2,A2=C2,A2=D2),AND(B2=C2,AND(D2>A2,D2<B2)),AND(C2>A2,AND(A2=B2,A2=D2))),"YES!!","NO!!")

Check your "betweens" for inclusive or not.

 

[Guest]

=IF(AND(A2=B2,A2=C2,A2=D2),"ok",IF(AND(B2=C2,D2>A2,D2<B2),"ok",IF(AND(A2=B2,A2=D2,C2>A2),"ok","bad")))

Vaya con Dios,
Chuck, CABGx3