Comparing Dates

[Guest]

I have 2 sheets. Sheet #1(Terminted Employees) has employee ids, names,
termination date, etc. Sheet#2 (New Hires) has employee ids, names, hire
date, etc. I will like to compare the dates in the 2 sheets. I need a
formula that will look at the termination date and the hire date. If the
termination date is earlier than the hire date, then it is not Ok. If the
termination date is later, then it is ok. I used the following formula but
I am not getting the desired result.
=IF(D3<Hires!E$2:E$975, "OK", "Not OK").
The formula is not comparing the same employee on both sheets.

Any help would be appreciated

Thanks

 

[Sandy Mann]

Assuming that the ids are in Column B then try:

=IF(SUMPRODUCT((Hires!B3:B975=B3)*(Hires!E3:E975>D3)),"OK","Not OK")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks for your help Sandy!
--
Fubdap

Assuming that the ids are in Column B then try:

=IF(SUMPRODUCT((Hires!B3:B975=B3)*(Hires!E3:E975>D3)),"OK","Not OK")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

You're welcome, thanks for the feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Thanks for your help Sandy!