Dashboard or Report from 2 custom date fields

[learningasana2024]

Hello,

I’d like to be able to pull a report or create a dashboard from two custom fields that are dates. This would be used to track the time it takes something to be completed. For example, time between start date and end date, both of which are custom fields. Is this possible?

[Danielle-GenD]

Hello @learningasana2024 Welcome to the forum. Just to confirm you are using custom fields rather than the Start date and End Date native ‘due date’ field.

You can do average time to complete currently but this might be a bit of a stretch

[Kevmoore]

Hello, I have a very similar question. I have a custom date field (“Requested timeline”) that I want to compare against the native “Completed On” date field:

How can I set up a chart in the dashboard to represent the performance of “Completed On” alongside the “Requested timeline”?

Use case is that “Requested timeline” is part of a form where customers can request an asset on a particular date. We want to understand historical performance of exceeding, meeting, or missing that initial target date.

[Kevmoore]

And I should clarify that this is different than the native “Time to complete” option in reporting. While that is valuable, I want to show time to complete against the initial target (“Requested timeline”). Hope this makes sense and that someone can assist!

[Richard_Sather]

Hi @Kevmoore , there may be a couple of ways to do achieve this but I have a few questions:

1. Which plan are you on?
2. What do you imagine the chart’s X-axis to be?
3. It sounds like you would want your Y-axis should be duration in time, right?

[Kevmoore]

We are on the Business/Advanced tier. X-axis could be Assignee, I suppose, or perhaps section. I believe the Y-axis should be duration in time, yes. The head-scratcher for me on this is trying to compare 3 different datasets in a 2-dimensional chart. I’m sure it can be done - just having writer’s block.

[Richard_Sather]

Hi @Kevmoore , so…

1. I asked about your tier because I recommend using formulas for this
2. It could be either, up to you, just checking
3. 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: