Calculate Difference in Sequential Dates in One Date Field

user0X

New member
Joined
May 5, 2018
Messages
22
Reaction score
0
Points
1
Excel Version(s)
2019
Calculate Difference in Sequential Dates in One Date Field

Existing Data Structure is as under:


SlNameGradeBranch Date RemarksDuration in GradeDuration in Branch
1Employee AGrade 2Branch 112-04-20154641081
2Employee BGrade 2Branch 212-04-2015
3Employee CGrade 2Branch 312-04-2016
4Employee AGrade 3Branch 118-07-2016Upgradation6181081
5Employee AGrade 4Branch 328-03-2018Upgradation & Transfer942440
6Employee BGrade 2Branch 112-04-2015
7Employee AGrade 4Branch 611-06-2019Transfer502

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
 

Attachments

  • Calculate Difference in Sequential Dates in One Date Field.xlsx
    9.6 KB · Views: 12
Please fill in the results you'd expect for the other employees in the sample.
 
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
GradeBranchDateRemarksDuration in GradeDuration in Branch
1
Employee AGrade 2Branch 112-04-20154641081
2Employee BGrade 2Branch 202-06-2014757757
3Employee CGrade 2Branch 312-04-2016635494
4Employee AGrade 3Branch 118-07-2016
Upgradation6181081
5Employee AGrade 4Branch 328-03-2018Upgradation & Transfer
942440
6Employee BGrade 2Branch 112-04-2015Transfer443314
7Employee AGrade 4Branch 611-06-2019Transfer942502
8Employee BGrade 3Branch 428-06-2016Upgradation & Transfer8091580
9Employee CGrade 2Branch 419-08-2017Transfer1411163
10Employee CGrade 3Branch 407-01-2018Upgradation10221163
11Employee BGrade 4Branch 415-09-18Upgradation7711580
 

Attachments

  • Calculate Difference in Sequential Dates in One Date Field.xlsx
    10 KB · Views: 11
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.
 
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
 
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
 
Just a 'be aware' note:
The following is a pic of your data and results for Employee B only:
2020-10-27_152009.png
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…
 
Would something like this be of any value?:
2020-10-27_171506.png
 
Last edited:
Just a 'be aware' note:
The following is a pic of your data and results for Employee B only:
View attachment 10127
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…


True, I guess I must consider restructuring my data to something more appropriately sensible. Also, its good to consider possible downgrades too.
 
Would something like this be of any value?:
View attachment 10128

The data in excel in its present structure is an input for Tableau so I will have to examine how the above modification can me displayed in Tableau. But certainly I should consider this structure too. Could you please post a sample xls?
 
Maybe its a good idea to split the dates into two columns. One as the date of joining a Branch and another as the date of joining the Grade. But what would haven if another parameter, say appointment at a Branch is added? Then that would need three date columns?

I thought it would be best to have only one column for dates, but I now see the drawbacks of such a structure.
 
Maybe its a good idea to split the dates into two columns. One as the date of joining a Branch and another as the date of joining the Grade. But what would haven if another parameter, say appointment at a Branch is added? Then that would need three date columns?

I thought it would be best to have only one column for dates, but I now see the drawbacks of such a structure.
Not necessarily, I ignored the Remarks column and treated each row as a snapshot in time, since there's a date, a branch and a grade in each row.
Let's say we're only interested in grades first:
If we sort your data by date and filter it just for one employee, and look at only the relevant columns:
2020-10-28_160603.png
We see he was grade 2 on 12/4/2015; it's the only information we have on him at grade 2 so I've assumed that's when he became grade 2, in any event it's the earliest info we have on him being grade 2.
We see he's grade 3 18/7/2016; same applies as above.
Now for grade 4 we see he has 2 dates, so I take the earliest leaving:
2020-10-28_161047.png
At this point I've assumed those dates are when he became those grades, so now I subtract consecutive dates from each other.
Actually I copy the dates from the row below to the row above (the last row doesn't have a row below it so I use today's date in that row instead), then subtract them:
2020-10-28_161603.png
Then lose a few columns leaving:
2020-10-28_162406.png
Then optionally we can transpose/pivot the last two columns to get:
2020-10-28_162641.png

Then we start all over again with Branches.

Assumptions made are (a) that he's never left the company and come back and (b) that the earliest date found when he was in a grade/branch is the date he started at that grade/branch.

That's the sort of thing I've done with Power Query. The attached contains messy queries because they're only work in progress and they're not tested, nor do I know the kind of output that suits you.
 

Attachments

  • ExcelGuru10883Calculate Difference in Sequential Dates in One Date Field.xlsx
    25.6 KB · Views: 10
Back
Top