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(D2A2, D2<B2)),AND(C2A2,AND(A2=B2,A2=D2))),"YES!!","NO!! ")

Check your "betweens" for inclusive or not.

--
Kind regards,

Niek Otten

"PAR" wrote in message ...
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(AND(A2=B2,A2=C2,A2=D2),"ok",IF(AND(B2=C2,D2A2 ,D2<B2),"ok",IF(AND(A2=B2,A2=D2,C2A2),"ok","bad") ))

Vaya con Dios,
Chuck, CABGx3



"PAR" wrote:

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