I was wondering if anyone could give me some tips on creating a Nintex workflow that will achieve the below goal of giving me a total number (in integers up to two dec place). I have a SharePoint list that tracks company store closures. I would like to achieve a total number of hours the Store was closed but I would prefer not to include non business hours (Sun-Thurs and Fri-Sat are known/set closed hours and do not differ in our Store locations.Here is what I was hoping for.
Compare difference between two date columns within SharePoint (so date/time Store closed and date/time Store reopened)
Exclude "closed/non business hours" and then get a total number with up to two decimal places to update the SharePoint list with.
I think my biggest struggling is determining which date types or functions to use in order to convert date/time and get the difference then subtracting based on number of days closed. I appreciate any tips on this!
Solved! Go to Solution.
@tracy yes you can do this with a workflow. To start you would need to calculate two different things. What is the total number of business hours or days in the range, then total number of hours. Then its a simple subtraction.
I will say it is not easy to figure out the closed/non-business hours because you will have to account for weekends which is a hassle. You may be able to achieve this by either dumping the data into Excel using the NETWORKDAYS formula https://exceljet.net/excel-functions/excel-networkdays-function and then extracting the date range from there. Nintex does not have a builtin function for this. There may be a cloud service that will allow you to pass in two data values like https://www.timeanddate.com/date/workdays.html.
I was not considering excluding closure a few times a year on holidays. I am more trying to get a decimal total of unplanned Store closure hours for reporting, if possible. I am actually trying to test the below method now to see if it will work for my purpose. I am super new to this stuff so this might not be the best approach I will admit.
Workflow steps I am testing
@tracy .....since you have store open date/time and store close date/time then can't you use a calculated column in SharePoint? You can use formula (=[Store_Close_Date_Time]-[Store_Open_Date_Time])....and use number data type with 2 decimals.
15 minute difference will be equal to 0.01.....See below:
I have been able to realize this all within SharePoint as you can see the many calc columns I have had to create below but but I think the issue of knowing number of days of closure is where its tricky. Thanks for everyones suggestions! The Nintex workflow I created and tested with yesterday seems to be working well - and if rounding does not work properly I can always ask end user for "number of nights Store was closed" to include in my calculation. Thank you for your help!