Page 2 of 2 FirstFirst 1 2
Results 11 to 14 of 14

Thread: Calculate Difference in Sequential Dates in One Date Field

  1. #11
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019


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

    Quote Originally Posted by p45cal View Post
    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. #12
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by p45cal View Post
    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. #13
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019
    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. #14
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by user0X View Post
    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:
    Click image for larger version. 

Name:	2020-10-28_160603.png 
Views:	6 
Size:	3.5 KB 
ID:	10130
    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:
    Click image for larger version. 

Name:	2020-10-28_161047.png 
Views:	6 
Size:	3.2 KB 
ID:	10131
    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:
    Click image for larger version. 

Name:	2020-10-28_161603.png 
Views:	8 
Size:	11.5 KB 
ID:	10132
    Then lose a few columns leaving:
    Click image for larger version. 

Name:	2020-10-28_162406.png 
Views:	8 
Size:	5.0 KB 
ID:	10133
    Then optionally we can transpose/pivot the last two columns to get:
    Click image for larger version. 

Name:	2020-10-28_162641.png 
Views:	8 
Size:	1.3 KB 
ID:	10134

    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 FirstFirst 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
  •