# Days Between 2 Dates with Criteria

1. ## 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.

2. ## 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.

3. ## Re: Days Between 2 Dates with Criteria

Originally Posted by MrShorty
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. ## 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. ## 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. ## 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.

7. ## 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. ## Re: Days Between 2 Dates with Criteria

Originally Posted by MarvinP
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

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

#### 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