This is my current dataset. I am trying to populate the output table to count how many employees in January 2021 were promoted because the month of January 2021 fell between their start and end date.
Output table:
I have tried a simple formula: =IF(AND(end_date>=Jan-21), SUM(employees,”0”) but it's not working. Could you please help?
Employee | start date | end date | Status |
1 | 2-Nov-20 | 2-Jun-21 | Promoted |
1 | 1-Mar-21 | 26-Mar-21 | Promoted |
1 | 28-Sep-20 | 30-Apr-21 | Not promoted |
1 | 5-Jan-2021 | 1-Jun-21 | Not promoted |
1 | 1-Feb-2021 | 28-Feb-21 | Promoted |
Output table:
Status | Jan-21 | Feb-21 | Mar-21 | April-21 |
Promoted | 1 | 2 | 2 | 1 |
Not promoted | 2 | 2 | 2 | 2 |
I have tried a simple formula: =IF(AND(end_date>=Jan-21), SUM(employees,”0”) but it's not working. Could you please help?