Employee Live or Not Live

[Amol lad]

Hi,

My Name is Amol.

I want to Find out the weather the employee was live in a particular month or not using their Date of Joining and Date of Leaving. Can anyone help me with the formula to use in excel. I am attaching the excel sheet for you reference.

 

[vletm]

Amol lad
Sample tries to show 'FALSE' if not ...
... I copied formula only top part of Your sheet.

 

[Juniad]

Try this in E2
=IF(ISNUMBER(MATCH(E$1,INDEX(ROW(INDIRECT($C2&":"&$D2)),,),0)),"Live","Not Live")

 

[rahulshewale1]

hi @Amol lad ,

Try This IN E2 Copy Across

=IF(AND($C2<=E$1,$D2>=E$1),"Live","Not Live")

 

[Amol lad]

Amol lad
Sample tries to show 'FALSE' if not ...
... I copied formula only top part of Your sheet.

Hi @vletm

Thank You for your reply...

I have done some changes in the formula. I have replaced "" with 1 and False with 0. (Live = 1, Not Live = 0)

i really like the way you derived this complex condition but the error is coming in some scenarios.

The scenarios are as below:

EMP ID	NAME	DOJ	DOL	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20
14415	Palash Khetrapal	08-Apr-19	04-Jul-19	0	1	1	0	0	0	0	0	0	0	0	0
14420	Anindita Ray	08-Apr-19	23-Apr-19	0	0	0	0	0	0	0	0	0	0	0	0

Explanation:-

1.Employee code 14415 joined on 08-Apr-19 and it means he was live in Apr-19 month but its coming 0 instead of coming 1
2.Employee code 14420 was joined on 08-Apr-19 and left on 23-Apr-2019 i.e. he joined and resigned in same month so he was live in Apr-19 but its coming 0 instead of 1.

I am attaching the File for your ready reference. Can you please help on this.

 

[Amol lad]

hi @Amol lad ,

Try This IN E2 Copy Across

=IF(AND($C2<=E$1,$D2>=E$1),"Live","Not Live")

Hi @rahulshewale thank for the reply.

What about the Employees who are resigned.

 

[Amol lad]

Try this in E2
=IF(ISNUMBER(MATCH(E$1,INDEX(ROW(INDIRECT($C2&":"&$D2)),,),0)),"Live","Not Live")

hi @Junaid Thanks for the reply.

i have used your formula but What about the Employees who are resigned. Its giving me the error there.

 

[vletm]

Amol lad
I missed that live in a particular month, that solved full months ...
check this version.

( with those two columns and one row would no need to have same part of formulas many times. )​

 

[Juniad]

hi @Junaid Thanks for the reply.

i have used your formula but What about the Employees who are resigned. Its giving me the error there.

what do u mean resigned .. and i could't find any error, Can u share screenshot with required result and error which is showing there.W