Total decimal between date ranges excluding known hours

  • 14 October 2019
  • 6 replies
  • 4 views

Badge +3

Hello,

 

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! 

 


6 replies

Badge +17

@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. 

Badge +12

@tracy .....are you including holidays or excluding them?

Badge +3

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.

Notes

  • Business hours are 10am-1am Sun-Thurs & 10am-1:30am Friday/Saturday
  • I am OK with not including weekends to simplify this and get it working

Workflow steps I am testing

  • set a single line text var with fn-DateDiffHours(storecloseddate/time,storeopeneddate/time)
    • To get the hours count between closure and reopened dates
  • set a single line text vTotalDays with fn-DateDiffDays(storecloseddate/time,storeopeneddate/time)
    • To get the number of days the store was closed
  • fn-Round(vTotalDays)
    • To round this number
  • Math calculation vTotalDays * 9
    • Multiply number of days closed by 9 which are the hours we are closed and I do not want to include in total count of hours closed
  • Math calculation to subtract difference in hours between closed and reopened minus vTotalDays to get vTotalBusinessHoursClosed(int)
Badge +12

@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:

 

4977i498307CBBC024ED0.jpg

 

Badge +3

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! 

4983i4707FA97E39A1342.png

Badge +3

This is working for us! I have end user providing number of nights closed and this Nintex wf is handling the rest. 

Reply