Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Calculate Difference in Sequential Dates in One Date Field

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

    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. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,487
    Articles
    0
    Excel Version
    Office 365 Subscription
    Is this only for Employee A?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by AliGW View Post
    Is this only for Employee A?
    No, Employee A is a just a specimen illustration. The data consists of hundreds of employees.

    Regards

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,487
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please fill in the results you'd expect for the other employees in the sample.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by AliGW View Post
    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. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,828
    Articles
    0
    Excel Version
    O365
    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. #7
    Seeker user0X's Avatar
    Join Date
    May 2018
    Posts
    12
    Articles
    0
    Excel Version
    2019
    Quote Originally Posted by Bob Phillips View Post
    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. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,828
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by user0X View Post
    Thanks for the solution. Apologies for the errors in the filled up examples.
    No problems, just pointed it in case.

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

    Quote Originally Posted by user0X View Post
    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. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    Just a 'be aware' note:
    The following is a pic of your data and results for Employee B only:
    Click image for larger version. 

Name:	2020-10-27_152009.png 
Views:	7 
Size:	10.0 KB 
ID:	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…

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,807
    Articles
    0
    Excel Version
    365
    Would something like this be of any value?:
    Click image for larger version. 

Name:	2020-10-27_171506.png 
Views:	12 
Size:	5.3 KB 
ID:	10128
    Last edited by p45cal; 2020-10-27 at 07:23 PM.

Page 1 of 2 1 2 LastLast

Posting Permissions

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