4 Replies Latest reply: May 15, 2014 9:52 AM by Nick Osdale-Popa RSS

    One Ugly Formula

    Nick Osdale-Popa

      My data is laid out like so:

      [font="courier"][*Pertinate Employee Data/Job Worked/etc*]

             SAT     SUN     MON     TUE     WED     THR     FRI

      R:    0.00    0.00    8.00    8.00    0.00    0.00    0.00

      O:    8.00    0.00    2.00    2.00    0.00    0.00    0.00

      D:    0.00   10.00    0.00    0.00    0.00    0.00    0.00[/font][/quote]I have 2 line detail template as such:

      Highlight the line with Sat~Fri and the first row of hours.

      Trap on SAT[/b]

      Capture the Line R: and all hours[/b] as one field

      In advance, End Line on Blank Field Values [/b]

       

      The reason for this is that I want to capture all hours in one line, rather than have 3 seperate lines. Also, some lines will have any combination of R, O, and D with a minimum of any one line and a maximum of all 3 lines.

       

      Next I needed to break out the individual types of hours (R,O,D[/b]), deliminate them (;[/b]), remove any extra spaces, and last but not least, move the SAT[/b] and SUN[/b] hours to the end of the string.

       

      Here's the formula that I used to accomplish that:

      (I like the fact that you can space out formulas like this for easy reading/editing)

      [font="courier"]Strip(

      If(Instr("R:",Hours)>0,

        Substr(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

          SubStr(Hours,Instr("R:",Hours)+2,56)

         ,49,1,";")

         ,41,1,";")

         ,33,1,";")

         ,25,1,";")

         ,17,1,";")

         ,09,1,";"),18,56)";"

        Left(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

         Stuff(

          SubStr(Hours,Instr("R:",Hours)+2,56)

         ,49,1,";")

         ,41,1,";")

         ,33,1,";")

         ,25,1,";")

         ,17,1,";")

         ,09,1,";"),16)

      ,"0.00;0.00;0.00;0.00;0.00;0.00;0.00")," ")[/font][/quote]The last part of the formula was to return a string of Zero hours, if that particular Hours type was not found.

       

      This formula was duplicated two more times change the R: to O: and D: where needed.

       

      Granted, I could have broken this out by making the Stuff() formulas as a seperate field, but that's just cheating.  

        • One Ugly Formula
          Nick Osdale-Popa

          Grant,

           

          I can't answer your PM because your Inbox is full. 

          • One Ugly Formula
            Grant Perkins

            Originally posted by Nick Osdale-Popa:

            Grant,

             

            I can't answer your PM because your Inbox is full.     /b[/quote]Darn - I'm sure it told me last time that happened ...        

             

            OK. I've made some space.

             

            Thanks for letting me know.

             

             

            Grant

            • One Ugly Formula
              Nick Osdale-Popa

              Looking at the new functions for Monarch 8 (I don't have my version yet!), this formula can be seriously improved with:

               

              InTrim()

              Replace()[/b]

               

              If someone has V8 to try this, I'd like to see the new formula.    smile.gif[/img]

               

              EDIT: I was thinking more about this and probably can do this without using the Replace()[/b] function.

               

              [size="1"][ April 13, 2005, 09:23 PM: Message edited by: Nick Osdale-Popa ][/size]

              • One Ugly Formula
                Nick Osdale-Popa

                Here's what I came up with using the New V8 Formulas:

                [font="courier"]Replace(

                  InTrim(

                    If(

                     Instr( "R:", Hours ) > 0,

                       SubStr( SubStr( Hours, Instr( "R:", Hours ) + 3, 56 ), 18, 56 )

                         + " " +

                         Left( SubStr( Hours, Instr( "R:", Hours ) + 3, 56 ), 16),

                       "0.00;0.00;0.00;0.00;0.00;0.00;0.00")

                  )

                ," ",";")[/font][/quote]