NESTED IF AND

[SDave]

Hi Guys,

I'm wondering if you could help me.

I have a nested IF AND formula which I can't get quite right - I must have been over it a million times.

Essentially what I'm trying to do is work out the status of an individual based on a date range.

The formula is:

=IF(ISERROR(IF(AND(C3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Hired and Terminated in Period",IF(AND(C3>=Headcount!$D$13,C3<=Headcount!$D$14),"Hired in Period",IF(AND(AC3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Terminated in Period",IF(AC3<Headcount!$D$13,"Terminated in Prior Period",IF(C3>=Headcount!$D$14,"Future Period Hire","Active in Period")))))),"",IF(AND(C3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Hired and Terminated in Period",IF(AND(C3>=Headcount!$D$13,C3<=Headcount!$D$14),"Hired in Period",IF(AND(AC3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Terminated in Period",IF(AC3<Headcount!$D$13,"Terminated in Prior Period",IF(C3>=Headcount!$D$14,"Future Period Hire","Active in Period"))))))

C3 = Hire Date
AC3 = Termination Date
$D$13 = Period Start Date
$D$14 = Period End Date

For the majority of people it seems to work fine.

I've got one record for instance for Joe Bloggs, whose:

Hire date is 03/08/2020
Termination date is 06/04/2020

The end result for Joe should be Active in Period, whereas it currently states Terminated in Prior Period.

Any help would be much appreciated.

Thanks,

Sam

 

[jasonb75]

The problem is I have to count prior period terminations - including rehires.

So the 'correct' result would be something on the lines of 'Terminated in prior period and rehired in current period'?

If you leave the previous termination date in place then it is going to cause problems, you would need to change that to reflect the termination date of the current hire, not the previously terminated hire for the formula to work correctly.

Also, for what it's worth, you don't need the first half of your formula(s). There is nothing in them that would result in an error.

 

[Fluff]

Maybe something like

=IF(AND(C3>AC3,C3<=Headcount!$D$13),"Active in period",IF(AND(C3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Hired and Terminated in Period",IF(AND(C3>=Headcount!$D$13,C3<=Headcount!$D$14),"Hired in Period",IF(AND(AC3>=Headcount!$D$13,AC3<=Headcount!$D$14),"Terminated in Period",IF(AC3<Headcount!$D$13,"Terminated in Prior Period",IF(C3>=Headcount!$D$14,"Future Period Hire","Active in Period"))))))

But I don't understand why you don't have a new row for every re-hire. By changing dates & leaving the old date for termination you are causing yourself problems.