+ Reply to Thread
Results 1 to 8 of 8

Days Between 2 Dates with Criteria

  1. #1
    Registered User
    Join Date
    03-05-2021
    Location
    NE
    MS-Off Ver
    365
    Posts
    10

    Days Between 2 Dates with Criteria

    Hi all,

    I'm hoping to get some help with the attached workbook. In the Days Between column I'd like to calculate the days between the latest date and the second-latest date, then the second-latest from third-latest, and so on -- all of this is tied to each unique Employee Number column. I tried setting it up with a ranking but unsure where to go from here. Any assistance appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Days Between 2 Dates with Criteria

    Are the records for each employee always grouped together like in this example? If so, something like =IF(A2=A1,B2-B1,0) seems to work.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    03-05-2021
    Location
    NE
    MS-Off Ver
    365
    Posts
    10

    Re: Days Between 2 Dates with Criteria

    Quote Originally Posted by MrShorty View Post
    Are the records for each employee always grouped together like in this example? If so, something like =IF(A2=A1,B2-B1,0) seems to work.
    Hello MrShorty,
    Yes -- but the data usually comes from a PowerQuery and I need the ability to sort without breaking things.
    Thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    374

    Re: Days Between 2 Dates with Criteria

    How about something like this in D3

    =IF(A3=A2,B3-B2,"") then copy down

    Basically it looks at Employee number and sees if it matches then subtracts the date from the above date.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Days Between 2 Dates with Criteria

    It might be useful to describe exactly how you are working with this data.

    I wonder if Power Query would be a better tool for this. Where you are already using Power Query to bring the data from some outside source into Excel, perhaps it would be easier to have Power Query perform this calculation. But, I don't know anything about Power Query, so someone else would need to speak to that possibility. I'll see if someone wants to come in and look at this possibility.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,442

    Re: Days Between 2 Dates with Criteria

    Hi mike,

    Seems like you want two different techniques in Power Query.
    1. https://www.goodly.co.in/refer-previ...w-power-query/
    2. https://eriksvensen.wordpress.com/20...g%20the%20step.

    I hope the above two links show. The idea is to bring down the date above to each row after sorting them.
    Then use the Duration function in PQ on each row.

    Hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,442

    Re: Days Between 2 Dates with Criteria

    Hi engxl,

    I've done what I suggested in the above post and here is the answer using Power Query.
    DaysRank using PQ.xlsx

  8. #8
    Registered User
    Join Date
    03-05-2021
    Location
    NE
    MS-Off Ver
    365
    Posts
    10

    Re: Days Between 2 Dates with Criteria

    Quote Originally Posted by MarvinP View Post
    Hi engxl,

    I've done what I suggested in the above post and here is the answer using Power Query.
    Attachment 726679
    This is great, thank you. Just have to look at the Editor and learn how to do it myself now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Total Amount of Days Between Two Dates Using Alternate Days as a Variable
    By williamome in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2021, 03:31 AM
  2. Sum days/hours between multiple dates in excel with criteria
    By Abe1992 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-02-2019, 10:29 PM
  3. [SOLVED] Calculating Years, Months, Week, Days, Hours, Minutes for Work Days between two dates
    By hecgroups in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-23-2019, 12:09 AM
  4. [SOLVED] Count days between dates with a criteria
    By Macfool in forum Excel General
    Replies: 3
    Last Post: 07-14-2016, 07:46 PM
  5. Replies: 0
    Last Post: 10-31-2014, 10:32 AM
  6. Count days between dates based on criteria
    By efernandes67 in forum Excel General
    Replies: 9
    Last Post: 06-23-2012, 04:29 PM
  7. Days between dates with selected criteria
    By mrcois in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-27-2011, 08:29 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1