# Average time to response but how do I not include weekends?

Options
Member

I am making a dashboard summarizing some trends with Jira tickets and trying to create a metric card that shows the current average time to first response, I am using this formula (which I think I got off this forum):

CONCAT(
case when ROUND((((86400DATEDIFF(`[CHART] Date of First Response`,`Created`))+(time_to_sec(TIMEDIFF(`[CHART] Date of First Response`,`Created`))))/60)/60 - 0.5, 0) <= 9 then '0' else '' end,
ROUND((((86400
DATEDIFF(`[CHART] Date of First Response`,`Created`))+(time_to_sec(TIMEDIFF(`[CHART] Date of First Response`,`Created`))))/60)/60 - 0.5, 0))

But we are finding out the average is quite high because of the fact that weekends will be an extra 48 hours to first response. How can I make this only weekdays?

Tagged:

• Coach
Options

You can use the DAYOFWEEK() function to identify and remove weekends - days 1 and 7 (saturday and sunday)

CASE WHEN DAYOFWEEK(`YourDateField`) NOT IN(1,7) THEN Your beastmode END

• Contributor
Options

Hey Deona,

Break this calculation down into steps:

1. Creation of ticket date
2. First response date
3. Days between creation and first response date
4. Weekend days between creation and first response date
5. Weekday days between creation and first response date.

1. The column is given
2. given
3. datediff(`creation_date`,`first_response_date`)
4. You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
5. Column #3 - column #4

• Contributor
Options

I think there's a way to do this by figuring out the day of week of the created field, the day of week of date of response, and the datediff of the two. You should be able to calculate how many weeks turned over from those three points of data, and therefore how many 2-day weekends which you can subtract within your beastmode, but I don't have enough time to play with it at the moment.

• Coach
Options

You can use the DAYOFWEEK() function to identify and remove weekends - days 1 and 7 (saturday and sunday)

CASE WHEN DAYOFWEEK(`YourDateField`) NOT IN(1,7) THEN Your beastmode END

• Contributor
Options

Hey Deona,

Break this calculation down into steps:

1. Creation of ticket date
2. First response date
3. Days between creation and first response date
4. Weekend days between creation and first response date
5. Weekday days between creation and first response date.

1. The column is given
2. given
3. datediff(`creation_date`,`first_response_date`)
4. You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
5. Column #3 - column #4

• Contributor