Forum Discussion
Solved
returning a 1 or 0 depending on start and termination dates
I have an issue with some code. I would like it to return a 1 or a 0 depending on whether the employee worked with the company that month.
the issues I have are as follows:
1. An employee starts on the 25th of the month, but the code doesn't count that month, so it returns a 0.
2. instead of returning a "0", I am getting "False".
=(NUMBERVALUE(F$1)<=IF($D2="", IF($E2="", DATE(9999, 12, 31), NUMBERVALUE($E2)), NUMBERVALUE($D2)))*(EOMONTH(NUMBERVALUE(F$1), 0)>=NUMBERVALUE($C2))
Fill down, then to the right (or vice versa)
4 Replies
The part
IF(NUMBERVALUE($C2)>NUMBERVALUE(F$1),IF(NUMBERVALUE($C2)<NUMBERVALUE(F$1),...
makes no sense: $C2 cannot be greater than F$1 and less than F$1 at the same time.
Don't you want to take the termination date into account?
HansVogelaar yes I realised after I posted that there was an additional bit copied that shouldn't be there. I'd like it to be a 1 if they were part of the company that month, and a "0" if they were not, either before or after employment.
I have changed the Code to include letters so I can see exactly which bit it's being pulled from.
=IF($D2<>"",IF(NUMBERVALUE($C2)>NUMBERVALUE(AP$1),"A","B"),IF(NUMBERVALUE($C2)<NUMBERVALUE(AP$1),IF(OR(NUMBERVALUE($E2)>NUMBERVALUE(AP$1), NUMBERVALUE($E2)="C"),"D","E"),"F"))
=(NUMBERVALUE(F$1)<=IF($D2="", IF($E2="", DATE(9999, 12, 31), NUMBERVALUE($E2)), NUMBERVALUE($D2)))*(EOMONTH(NUMBERVALUE(F$1), 0)>=NUMBERVALUE($C2))
Fill down, then to the right (or vice versa)
