# Thread: Calculate Difference in Sequential Dates in One Date Field

1. ## Calculate Difference in Sequential Dates in One Date Field

Register for a FREE account, and/
or Log in to avoid these ads!

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

2. Is this only for Employee A?

3. Originally Posted by AliGW
Is this only for Employee A?
No, Employee A is a just a specimen illustration. The data consists of hundreds of employees.

Regards

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

5. Originally Posted by AliGW
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

6. 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.

7. Originally Posted by 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.
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

8. Originally Posted by user0X
Thanks for the solution. Apologies for the errors in the filled up examples.
No problems, just pointed it in case.

Originally Posted by user0X
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).

Originally Posted by user0X
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

9. 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…

10. Would something like this be of any value?:

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•