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

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

Calculate Difference in Sequential Dates in One Date Field

Existing Data Structure is as under:

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.

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.

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

and

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

and

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
•