0

I have a rather complicated (to me) Date/Time question. I have a multi-step workflow, and am trying to build two calculated columns to track the elapsed time in business hours (M-F, 0800-1700) between two steps in the workflow. Here is where it gets tricky though, I also want to ensure I do not penalize the tasked user for any changes/modifications made by the requester. So, in this workflow, a supervisor creates a record... which is then processed by security. IF security rejects the record (maybe they need more info?) then the supervisor gets notified via the workflow. So, when they save the updates a field called "[Supervisor Modified Time (Security)]" gets date/time stamped. So, the first thing I do is check to see if that field is BLANK. If it is, I use the [Created] field as the start date/time. If the field is NOT blank, I use it as the start date/time. Now, the calculated field is working perfectly...and displays total Hours:Minutes, BUT, I am having a hard time getting it to NOT include time elapsed over the night and weekends.

Here is my calculated column so far... which works for straight time:

    =INT(
         ([Security Completed Time]
          -IF( ISBLANK( [Supervisor Modified Time (Security)])
               ,Created
               ,[Supervisor Modified Time (Security)])
             )*24
         )
         &":"
         &MINUTE([Security Completed Time]
                 -IF(ISBLANK([Supervisor Modified Time (Security)])
                     ,Created
                     ,[Supervisor Modified Time (Security)]
                    ) 
       )

If it helps, the basic calculation without field names and without the checks for ISBLANK looks like this:

    =INT((A-B)*24)&":"&MINUTE(A-B)
0

I think you can not solve this with a simple calculated field. I would recommend:

  1. Create a column to store the difference of dates. It can be a numeric column.

  2. Create a C # event handler to perform this calculation. After the calculation, update the value of the column. This can be done in the onItemAdded and onItemUpdated event.

Within the event handler you have all the power of C #. This will make it easier to perform a complex calculation.

  • I only have Site owner permissions and access to sharepoint designer 2016 – njphillips01 Jun 23 '17 at 21:26
  • That is a problem. You cannot use an event handler. I think you can try with javascript approach... – Juan Pablo Pussacq Laborde Jun 26 '17 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.