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

 

[Fluff]

What are the period dates when you get that problem?

 

[SDave]

Hi Fluff,

The period dates are - start 01/09/2020 - end 30/09/2020.

Thanks,

Sam

 

[jasonb75]

Then it would appear that the formula result is correct, perhaps the termination date is wrong?

 

[Fluff]

In post#3 it looks as though you are using dd/mm/yy style dates, in which case the termination date is before the start date.

 

[SDave]

Hi Jason/Fluff,

It's a bit of weird one.

Joe in this case is a rehire. I've tried re-sequencing the formula/changing the order of the clauses, but that doesn't seem to work. Essentially if the hire date > termination date, which in this case it is, the result should read Active in Period.

I hope that makes sense.

Thanks,

Sam

 

[Fluff]

Surely if he's a re-hire, then the termination date should be blank as he hasn't yet been terminated for this period of employment.

 

[SDave]

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

I've essentially got two columns - This Period Status and Prior Period Status.

The Prior Period Status is essentially the same formula, the only difference being the Period Start and End Date, which is 01/08/2020 - 31/08/2020.

For Joe, his prior period status should be Hired in Period - his hire date falls between the prior period start and end date. His this period status should be Active in Period.

This period formula:

=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"))))))

Where $D$14 = 01/09/2020
$D$15 = 30/09/2020
C3 = Hire Date (03/08/2020)
AC3 = Termination Date (06/04/2020)

Prior period formula:

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

Where $D$15 = 01/08/2020
$D$16 = 31/08/2020
C3 = Hire Date (03/08/2020)
AC3 = Termination Date (06/04/2020)

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[SDave]

Hi Fluff,

I've updated my details. I'm using Excel for Mac - part of Office 365. Version 16.39.

Thanks,

Sam