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: