Calculate number of days difference between most recent date and other date based on unique ID

I would like to calculate the difference in number of days  between 2 dates based on unique ID's.

Here is a sample of my date:

ID        Interview         Date Interview        Date Planned         Number of Days

ID 1     Interview 1      01/05/2017              15/04/2017
ID 1     Interview 2      05/05/2017              15/04/2017  
ID 1     Interview 3      12/05/2017              15/04/2017             ??
ID 2     Interview 1      10/04/2017              13/03/2017
ID 2     Interview 2      15/04/2017              13/03/2017             ??


In the fields marked by question marks I would like to see the difference between the latest 'Date Interview' and 'Date Planned' for ID 1 and ID 2 and all other ID's I have in my file.

In my example we can clearly see that '12/05/2017' is the latest date for ID 1 but can I make it so that in the column 'Number of Days' I get the difference in days between the latest 'Date Interview' and 'Date Planned' for the respective ID ?

Thanks.
Antonio Lopez RamirezProject Coordinator Back OfficeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Hi Antonio,

Can you upload a sample file with some examples? Also, do you want calendar days or working days?
0
Antonio Lopez RamirezProject Coordinator Back OfficeAuthor Commented:
Hi Rob,

I uploaded the example file. Calendar days please.

Thank you,
Antonio
Interviews-in-progress.xlsx
0
Fabrice LambertFabrice LambertCommented:
Could the DAYS360 formula perform the work ?
=DAYS360(C1,D1)

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
Can you confirm which dates you want to compare?

For example ID 1: Compare 15/04/17 with 01/05/17 or Compare 15/04/17 with 12/05/17, ie planned date and 1st actual date or most recent actual date.
0
Rob HensonFinance AnalystCommented:
In simplest form, formula in E2 and copied down:

=IF(A2=A3,"",C2-D2)

This assumes that each of the IDs grouped and sorted in date order. This gives result of 27 against ID 1 and 33 against ID 2.

Thanks
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Antonio Lopez RamirezProject Coordinator Back OfficeAuthor Commented:
Hi Rob,

I would like to compare the latest 'Interview date' against the 'planned date' for that concerning ID.

Thus for ID 1 that would be 12/05/2017 against 15/04/2017  = indeed 27 days.

So for every ID, I would like to have it check what the latest date is of that ID's interviews and compare it against the 'planned date'
0
Antonio Lopez RamirezProject Coordinator Back OfficeAuthor Commented:
Hi Rob,

I used your solution and it worked!

The prerequisite is that the data needs to be sorted based on ID and date for it to work.

Thanks you!
0
Antonio Lopez RamirezProject Coordinator Back OfficeAuthor Commented:
Great help!!
0
Rob HensonFinance AnalystCommented:
To overcome the pre-requisite of the data being grouped, you could also use a pivot table. The entries would still have to be in Date order so that the most recent entry for each ID is the last in the list for that ID. I assume this would occur naturally anyway as you add further entries to the list.

See attached.

In the Days column (changed to Check) I have put formula:

=IF(COUNTIF(A2:A$2,A2)=COUNTIF($A$2:$A$7,A2),"x","")

This uses COUNTIF to see if the entry is the last one. The first COUNTIF only counts the current and previous rows for occurrence of the ID whereas the second COUNTIF counts the whole table.

The Pivot Table then uses the check column to filter out the blanks and uses the MAX value of the two dates to populate those two columns and a Calculated field deducts Planned Date from Actual Date to give number of days.
Interviews-in-progress--1-.xlsx
0
Antonio Lopez RamirezProject Coordinator Back OfficeAuthor Commented:
I have another more advanced question related to this one. Is it possible to translate my IF formula to Power BI (M language) ?

Thanks
0
Rob HensonFinance AnalystCommented:
Sorry don't know anything about Power BI
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.