Calculate Difference in Sequential Dates in One Date Field

[user0X]

Calculate Difference in Sequential Dates in One Date Field

Existing Data Structure is as under:

Sl	Name	Grade	Branch	Date	Remarks	Duration in Grade	Duration in Branch
1	Employee A	Grade 2	Branch 1	12-04-2015		464	1081
2	Employee B	Grade 2	Branch 2	12-04-2015
3	Employee C	Grade 2	Branch 3	12-04-2016
4	Employee A	Grade 3	Branch 1	18-07-2016	Upgradation	618	1081
5	Employee A	Grade 4	Branch 3	28-03-2018	Upgradation & Transfer	942	440
6	Employee B	Grade 2	Branch 1	12-04-2015
7	Employee A	Grade 4	Branch 6	11-06-2019	Transfer		502

Requirement:
A formula is required to calculate the total duration an Employee spent in a particular grade and branch.
The value is to be entered in a separate columns called 'Duration in Grade' and 'Duration in Branch'

I guess a conditional DATEDIF should meet the requirement.

Thanks in Advance
Regards

[AliGW]

Is this only for Employee A?

[user0X]

Is this only for Employee A?

No, Employee A is a just a specimen illustration. The data consists of hundreds of employees.

Regards

[AliGW]

Please fill in the results you'd expect for the other employees in the sample.

[user0X]

Please fill in the results you'd expect for the other employees in the sample.

I have manually filled the values & reattached the sample file.

Sl	Name	Grade	Branch	Date	Remarks	Duration in Grade	Duration in Branch
1	Employee A	Grade 2	Branch 1	12-04-2015		464	1081
2	Employee B	Grade 2	Branch 2	02-06-2014		757	757
3	Employee C	Grade 2	Branch 3	12-04-2016		635	494
4	Employee A	Grade 3	Branch 1	18-07-2016	Upgradation	618	1081
5	Employee A	Grade 4	Branch 3	28-03-2018	Upgradation & Transfer	942	440
6	Employee B	Grade 2	Branch 1	12-04-2015	Transfer	443	314
7	Employee A	Grade 4	Branch 6	11-06-2019	Transfer	942	502
8	Employee B	Grade 3	Branch 4	28-06-2016	Upgradation & Transfer	809	1580
9	Employee C	Grade 2	Branch 4	19-08-2017	Transfer	141	1163
10	Employee C	Grade 3	Branch 4	07-01-2018	Upgradation	1022	1163
11	Employee B	Grade 4	Branch 4	15-09-18	Upgradation	771	1580

[Bob Phillips]

I think some of your worked examples are wrong, so on that basis, try these array formulae

=IFERROR(INDEX($H3:$H$13,MATCH(1,($E3:$E$13=$E2)*(($I3:$I$13="Upgradation")+($I3:$I$13="Upgradation & Transfer")),0)),TODAY())-$H2+1

and

=IFERROR(INDEX($H3:$H$13,MATCH(1,($E3:$E$13=$E2)*(($I3:$I$13="Transfer")+($I3:$I$13="Upgradation & Transfer")),0)),TODAY())-$H2+1

Note that the end of the range (e.g. $H3:$H$13) is one row beyond the actual data. This is to cater for the last row, and not get 1.

[user0X]

I think some of your worked examples are wrong, so on that basis, try these array formulae

=IFERROR(INDEX($H3:$H$13,MATCH(1,($E3:$E$13=$E2)*(($I3:$I$13="Upgradation")+($I3:$I$13="Upgradation & Transfer")),0)),TODAY())-$H2+1

and

=IFERROR(INDEX($H3:$H$13,MATCH(1,($E3:$E$13=$E2)*(($I3:$I$13="Transfer")+($I3:$I$13="Upgradation & Transfer")),0)),TODAY())-$H2+1

Note that the end of the range (e.g. $H3:$H$13) is one row beyond the actual data. This is to cater for the last row, and not get 1.

Thanks for the solution. Apologies for the errors in the filled up examples.
The formula provided above works well except it only considers values below the row the formula is being entered in. This would work only if the dates are listed chronologically.
Btw what does the value 1 at the start of the match formula do?

Regards

[Bob Phillips]

Thanks for the solution. Apologies for the errors in the filled up examples.

No problems, just pointed it in case.

The formula provided above works well except it only considers values below the row the formula is being entered in. This would work only if the dates are listed chronologically.

This is true. I think it would be far more complex a solution if they weren't sorted, so I suggest that you keep them sorted by date (and by employee).

Btw what does the value 1 at the start of the match formula do?

I am testing a couple of conditions, employee and remarks, and when I use the AND operator (*), it creates an array of s and 0s, so the 1 is matching against the first 1 in that array, in other words the first instance of a match for the employee and the target remark.

Regards

[p45cal]

Just a 'be aware' note:
The following is a pic of your data and results for Employee B only:

Realise you can't easily find how long he's been in grade 2 altogether; you have to find the row he was earliest in grade 2 and look at the value there (757), because that value includes all the 443 days in the next row (which is the number of days more that that employee will be in the same grade on that date). If you filtered for grade 2 and Employee B, you couldn't sum the number of days in that column, it'd be meaningless.

At some point I'll try and put a power query solution together, and try to make it cope with both upgrades and downgrades, so that someone being downgraded is handled too, even if they're subsequently upgraded again…

[p45cal]

Would something like this be of any value?: