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

Originally Posted by p45cal
Just a 'be aware' note:
The following is a pic of your data and results for Employee B only:
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.

2. Originally Posted by p45cal
Would something like this be of any value?:
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?

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

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

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:

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:

Then lose a few columns leaving:

Then optionally we can transpose/pivot the last two columns to get:

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.

Page 2 of 2 First 1 2

#### Posting Permissions

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