given the following excel table I'm trying to add a column with the number of days in a position, my problem is that the 'starting date' for an employee (column b) is the same. This is why I'm struggling with column D creation. For example: emp 111 the first row is DAYS(B2,C2), but for the second row should be (C2,C3). I tried to do it with index match array but I can't make it work.
How do I do this kind of calculation?
| emp_number | start_working_date | promotion_date |
|---|---|---|
| 111 | 2020-01-01 | 2020-02-04 |
| 111 | 2020-01-01 | 2020-04-23 |
| 111 | 2020-01-01 | 2020-08-20 |
| 112 | 2020-03-01 | 2020-05-01 |
| 112 | 2020-03-01 | 2020-07-01 |
| 113 | 2020-04-01 | 2020-08-01 |
| 114 | 2020-05-01 | 2020-09-01 |
| 115 | 2020-06-01 | 2020-10-01 |
CodePudding user response：
Use an IF with MATCH to test if it is the first or not, then subtract from the correct cell:
=IF(MATCH(A2,A:A,0)=ROW(),C2-B2,C2-C1)
Note this only works if the data is sorted correctly. Emp_Number then Promotion_Date.
If the data is not sorted then we can use:
=IF(COUNTIFS(A:A,A2,C:C,"<"&C2)=0,C2-B2,C2-MAXIFS(C:C,A:A,A2,C:C,"<"&C2))
The obligatory discussion on the difference between *IF(s)() and MATCH(). Match is much less intensive on the calcs and as such if it can be done sorting the data and using the first formula will be much more performant.
It will not be noticeable on a dataset of less than a couple thousand. But once the dataset gets above 10,000 the calc times will increase and at some point will fail when using the *IF(s)() function.
CodePudding user response：
You may also try using this, but in your query you have mentioned it wrong since, the syntax of DAYS Function is DAYS(end_date,start_date) where as you have provided as DAYS(start_date,end_date).
• Formula used in cell D2
=IF(COUNTIF($A$2:A2,A2)=1,DAYS(C2,B2),DAYS(C2,C1))
