3 Replies Latest reply: May 15, 2014 10:04 AM by Data Kruncher RSS

    Elapsed Workable Hours

    upattc _

      I need to calculate the amount of time that has elapsed between when a loan officer loads a loan into the system and when they decision it.  So I have a date and time for the start and end.  But my problem is they only want the "workable" hours, which means I need to subtract out or not include the time that the branch is not open (like on Sundays, holidays, from 7pm til 8am the next day).  I was able to create something in Excel but it's not pretty.  Any suggestions on how I could do this using Monarch???



      Callie Patton

        • Elapsed Workable Hours
          Olly Bond

          Hello Callie,


          In Monarch v9, a new function "Working_Days()" was introduced to help solve this kind of problem.


          You will need to tell Monarch what public holidays you want it to exclude, but once you do that you should have something that's a lot prettier than Excel.


          Best wishes,



            • Elapsed Workable Hours
              upattc _


              I thought I had seen or read about that function which is what gave me the idea of moving my work out of excel and into Monarch.  But we are on V9.01 and I can't find that function.




                • Elapsed Workable Hours
                  Data Kruncher

                  As much as Olly succinctly said,


                  You will need to tell Monarch what public holidays you want it to exclude[/quote]


                  There are a few steps that are required to do that.


                  First, you need to import a number of user defined functions from the functions master. On a standalone Monarch installation, you'll normally find this model file at C:\Documents and Settings\All Users\Documents\Monarch\Models\UserDefinedFunctions.xmod. Monarch should locate the file automatically when you want to Import user defined functions.


                  You'll need to import the related WorkingDays functions, and then customize the formulas for ComputeDaysOffInRange, IsWorkingDay, FirstValidDateForWorkingDays, and LastValidDateForWorkingDays.


                  Finally, you can then use Working_Days() in a new calculated field.


                  Even with the moderate level of effort required to use Monarch's function, it must be far easier than attempting to do the same accurately in Excel.


                  That said, I personally haven't had the need to do it yet, but I'm confident that the function has been tested thoroughly and is reliable.