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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Fabrice LambertFabrice LambertCommented:
Could the DAYS360 formula perform the work ?
=DAYS360(C1,D1)

Open in new window

0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.