ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Time difference between working Business Hours

MikeIves
7 - Meteor

Need help with a particular formulae or series of steps please. I have had a look through the community pages and found some help, but nothing really seems to fit the bill and was wondering if you would be issuing an ‘official’ formula as an industry standard like in other products?

 

We are using Alteryx to plug into our Service Desk and manipulate our data in various ways.

 

In one of those ways we need to work out the number of Minutes a ticket has been open. This is very simple in Excel etc., but we need Alteryx to do it.

·        Between 2 dates (Opened and Closed)

·        During Business Hours (9am-5pm), 5 days a week (should be able to change this if need be - 9am-6pm for example)

·        Excluding weekends and holidays (holidays should be a text file with ability to load as a separate file with holiday dates in).

 

I have attached a file with some data on it.

Open date column – M

Closed date column – Q

Result in minutes to be put in Time On Call column – AT.

BenMoss
17 - Castor
17 - Castor

Hey Mikelves,

 

Check out this macro I have built with your specifications which allows you to specify working days, working hours and also give a file which removes bank holidays for instance.

 

Note that at present I have built it out to work exactly with the structure of the template dataset given but I will develop it further tomorrow so it can be applied for any file when two date fields are specified.

 

Hope this fits well with what you want and let me know if you have any troubles with it.

 

I have also included an example workflow of the macro in action.

 

Regards,

Ben

MikeIves
7 - Meteor

Good morning Ben,

 

Thank you for your reply and for looking into this and for your valuable time in development of a solution. Your macro seems to work really well and what you further propose would be really extremely useful, in fact critical to us. 

 

We envisage using the solution in quite a few instances across many tables in the Service Desk. Not just to calculate Incident downtime, but also Change Implementation times, Problem Solution times and Service Request times but to name a few!

 

Thank you

Mike

BenMoss
17 - Castor
17 - Castor

Great that's good to here!

 

I have just published a first draft of the macro to the alteryx gallery that can be found here: https://gallery.alteryx.com/#!app/Working-Day-Time-Difference/58ca4b52f499c70d987f65dc which allows you to select two specific date/time fields so is no longer solely applicable to that single dataset you passed.

 

I'll be writing a how to post later this evening which i'll post in here :).

 

Appreciate any feedback you can provide so we can develop it further.

 

Ben

MikeIves
7 - Meteor

Thank you again Ben,

 

I'll get the team to give it a trial run and get back to you as soon as with any feedback!

 

Many thanks

Mike

MikeIves
7 - Meteor

Hi Ben,

 

Having a little trouble running the Macro. Getting the following error:

 

Failure to Import C:\Users\mike.ives\Documents\Analtyics Training Courses\Alteryx Training Files\Useful Macros and Formula\Working+Day+Time+Difference.yxzp.
There was an error opening "C:\Users\mike.ives\Documents\Analtyics Training Courses\Alteryx Training Files\Useful Macros and Formula\Working+Day+Time+Difference\Working Day Time Difference.yxmc": This document was created by a more recent version of this application

 

Any help would be much appreciated.

Mike

BenMoss
17 - Castor
17 - Castor

Arrr. Yes I created the macro in the latest version of Alteryx (11).

 

At some point today I will find some time to downgrade the macro and report here 🙂

 

Ben

MikeIves
7 - Meteor

Many thanks Ben 😉

BenMoss
17 - Castor
17 - Castor

Hey Mikelves,

 

New link for you. I've downgraded it with an xml hack. I hope it works.

 

Ben

 

https://gallery.alteryx.com/#!app/Working-Day-Time-Difference/58cb165ff499c70d987fe7f1

MikeIves
7 - Meteor

Thank you Ben,


Will give it a whirl.

 

Best regards

Mike

Labels