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.
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.
Originally Posted by shg
Hello MrShorty,
Yes -- but the data usually comes from a PowerQuery and I need the ability to sort without breaking things.
Thanks.
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.
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.
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.
Hi engxl,
I've done what I suggested in the above post and here is the answer using Power Query.
DaysRank using PQ.xlsx
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks