Hi @Kevmoore , so…
- I asked about your tier because I recommend using formulas for this
- It could be either, up to you, just checking
- Thanks for confirming for Y-axis to be duration and this is really the key.
So I would suggest you create a formula to simply derive the duration, or time it takes between the requested timeline date (I’m going to refer to it as the ‘Requested by’ date field) compared to the completed on date, as such:
However, you will find that the problem with this basic formula is that the ‘Requested by’ date will count from 02:00am of that day (at least for me it does - not sure if the reference is GMT cos I’m coincidentally located in +2 GMT timezone).
So the problem is that even if someone completes a task on the day of ‘Requested by’, say at 12 midday, the result will appear as if they were 10 hours late. But fear not…
To offset this issue and make things more fair to your assignees, you could assume that the ‘Requested by’ date means by 17:00 on that day. Therefore, we have to create an advanced formula to compensate for this discrepancy, while also formatting the result in number of days, with decimals. (You could also have the result in hours, if you prefer; simply replace 1440 in the below formula with 60, instead).
The advanced formula would look like the below, where (15 * 60) is compensating for how many hours to add to 02:00 to get to 17:00 o’clock. i.e. 17-2=15
And this is what the resulting project would look like, ideally with only one of the two formulas described above (I’m just showing the difference between the basic and advanced formulas):
So in your resulting chart in the dashboard, you could use this formula field as your Y-axis in a chart (likely set to average, right?) and your X-Axis would be your assignees:
