I saw through your attached data. You want to Find Difference between days for what? I see that for each Id , you have listed either Phase1 or Phase2. so what is required?
In your dataset ID is never repeating in any given month. Can you give a dummy data which matches your requirement.
Still IDs are unique within a month.
That shouldn't be the problem, i just want to know, if there is a way to calculate the difference in days between the phases for each id.
Yes you can use lookup function to achieve this. Lookup function can be used to use the data of next or previous rows.
If you can give some mock data and expected output of that data, Community can help.
thanks for your answer. The fact that the ids are unique within a month should be relevant. Take ID 2 in the picture for example. The dataset contains 3 phases. for my diagramme i want to:
- calculate the number of days it took to finish the phases. So if Phase 1 started 05.11.2017 and Phase 2 started 10.12.2017 the value should be 35, the date difference between these two dates. These values should be calculated for the following phases accordingly
- The date of Phase 1 is the start date for every ID. It is used for the ranking on the y axis
- the individual amount of days it took to complete every phase are stacked, so that the complete bar represents the total amount of time it took to complete the project
ideally, if you suggest, that you optimise your workflow with every project the bar should decrease over time. this is the purpose of the diagramme. to show wheather the total project time splitted into each phase, gets shorter over time.
Hopefully this is explained a little bit better.
Thanks for your help!