" IF And" statement

[rmrossetti]

Hello! Trying to build a formula that counts an employee as 1 if they are active and had a start date prior to the month in cell E3, F3, G3 and so on. If they are termed, than I would like to pull a 0 in subsequent periods, but keep the 1 where there were active. For instance, on john doe, I would like to show them as 1 in January, 1 in February, but 0 in March. This is what I entered, but something isn't clicking for me. =IF(AND($C5="",$B5<F$3),1,0), the problem is this will also result in 0 for January since it's true, false. Thank you!

EDIT:
Sorry, quick note, I would like to show them as 0 in March, but count them in Jan and Feb, since the term sate was end of the month in Feb.

 

[Coyotex3]

Maybe this is what you are looking for? A bit confused based on the quick note you posted.

PERSONAL.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Employee	Start Dat	End Date	Status	Jan-24	Feb-24	Mar-24	Apr-24	May-24
2	John Doe	1/1/200	2/28/2024	Termed	1	1	0	0	0
3	Jane Doe	1/4/2021	3/2/2024	Active	1	1	1	1	1
4	John Wick	6/1/2021	4/4/2024	Active	1	1	1	1	1
5	Mike Jones	6/3/2023	3/1/2024	Termed	1	1	1	0	0
6
7
8
9
10
11
12
13
14
Sheet4

Cell Formulas
Range	Formula
E2:I5	E2	=IF(AND($D2="Termed",$C2<E$1),0,1)

 

[rmrossetti]

=IF(AND($D2="Termed",$C2<E$1),0,1)

This looks great! Thank you! Might be getting greedy, but is there also a way to split the month? For instance, if termed on the 15th, is there a way to have them count as .5?