8 Replies Latest reply: May 15, 2014 10:00 AM by Grant Perkins RSS

    Adding Minutes and Seconds

    JCC _

      I have a mainframe report that contains a duration column in minutes and seconds formatted as mm:ss. I need to total these but am not sure which format to use in order to do that. Everything i have looked at sees that as hours and minutes which is not correct. How do I format this in order to get a sum of the minutes and seconds. Any assistance is greatly appreciated. Examples are:14:10, 3:52, 0:28. V 7.00 PRO

        • Adding Minutes and Seconds
          Data Kruncher

          Hi JCC,

           

          I came up with a decidedly inelegant solution to this, but it works. Perhaps a better solution can be achieved.

           

          This requires a two pass solution. The first pass totals the minutes and the seconds. The second pass determines the proper minutes and seconds, factoring in 60 seconds per minute.

           

          In the first model, capture the Duration field as a character field.

           

          Now build a calculated numeric PreMinutes field:

          [SIZE=2]val(lsplit(Duration,2,":",1))[/SIZE][/code]

           

          and a similar PreSeconds field:

          [SIZE=2]Val(LSplit(Duration,2,":",2))[/SIZE][/code]

           

          Now build a summary with a grand total line reading "Summary" for the PreMinutes and PreSeconds fields.

           

          Export the summary to a fixed-length text file. If you can open an Excel file as a database source in your version of Monarch, you could do that too and save some modeling in the second pass.

           

          Either way, open the output file as an input for the second pass and filter your results to only show the "Summary" record.

           

          Finally build a numeric Minutes field:

          [SIZE=2]if(>59,Int(/60)+,[Pre Minutes])[/SIZE][/code]

           

          and another Seconds field:

          [SIZE=2]mod(,60)[/SIZE][/code]

           

          and hide the PreMinutes and PreSeconds fields.

           

          It's done, but it feels convoluted even to me.

           

          Anyone else have a better way?

           

          Kruncher

            • Adding Minutes and Seconds
              Olly Bond

              Hi JCC,

               

              I'm not sure that two passes are needed here. If you can email me a sample report I'll happily have a quick look to see if we can get the result in one model. Assuming you trap Duration as a character field, you might try making the field into hh:mm:ss by simply defining a calculated field in Monarch as

               

              ctod("00:"+Duration;"y/m/d";"T")

               

              Best wishes,

               

              Olly

                • Adding Minutes and Seconds
                  JCC _

                  Thanks Data and Olly for all of your sugestions. I finally got something to work with a combination of both of your suggestions. Olly, for some reason i could never get your idea to work right but I am sure it was something I wasn't doing correctly. My method was with these calculated fields.

                  Val(Trim(Left(DUR,Instr(":",DUR)-1))) and

                  Val(Trim(Right(DUR,Len(DUR)-Instr(":",DUR))))

                  Thanks again for your support.

                   

                  John

                    • Adding Minutes and Seconds
                      JCC _

                      Ok, so here is the next problem. If I subtotal on items, the summary will display this:

                      Item          Minutes      Seconds

                      B1X005               1            212

                      Which is correct. So can any further calculations take place within the Summary above such as converting the seconds to MM:SS and then adding that to the Minutes subtotal?

                       

                      This is my idea derived from Data's formula but doesn't work for me:

                      if(>59,Int(/60)+,[Minutes])

                       

                      Sample data below.

                      ITEM  Minutes Seconds

                      B1X005       0      9

                      B1X005       0     22

                      B1X005       0      9

                      B1X005       0     24

                      B1X005       0     13

                      B1X005       0     16

                      B1X005       0      9

                      B1X005       0      9

                      B1X005       0      9

                      B1X005       0      9

                      B1X005       1     50

                      B1X005       0     33

                      • Adding Minutes and Seconds
                        henryiriawan _

                        Hi John,

                         

                        I just want to let you know that after I compared the result, Kruncher's idea and your idea has the same result.

                         

                        Val(Trim(Left(DUR,Instr(":",DUR)-1)))  equals to

                        val(lsplit(DUR,2,":",1))

                         

                        AND

                         

                        Val(Trim(Right(DUR,Len(DUR)-Instr(":",DUR)))) equals to

                        Val(LSplit(DUR,2,":",2))

                         

                        Edit:

                         

                        if(>59,Int(/60)+,[Pre Minutes])

                         

                        This method, I think, would be good if the "Pre Seconds" field exceed 59, for example, if it is 73 seconds, so, it would add 1 to "Minutes" Field and 13 seconds (as the mod result) on the Seconds field.

                         

                        So, I believed that this method will be good on base on record per record, not summary part.

                          • Adding Minutes and Seconds
                            Data Kruncher

                            The concept of calculating the proper minutes and seconds in the Summary is ideal, but...

                             

                            Monarch doesn't currently allow for many functions (specifically, in this case the Int(), or even the Floor() function), in the calculation of Summary fields, only certain aggregations and statistical functions.

                             

                            So I still don't see how one can calculate this properly with a single model.

                              • Adding Minutes and Seconds
                                Grant Perkins

                                John,

                                 

                                You really need to convert everything to seconds, sum the seconds and then present it formatted. Nominally you would present as a TIME field (part of a DATE field) but that would typically convert to hh:mm:ss which may not be what you want.

                                 

                                There were a couple of useful advances, iirc, in V8 and V9 for time related stuff compared to V7 but whether that makes a difference to your needs I can't recall off the top of my head.

                                 

                                 

                                Grant

                                  • Adding Minutes and Seconds
                                    Grant Perkins

                                    I think I don't have a working V7 at the moment but I do have a working V9. You may struggle with a couple of the functions here but there are probably alternatives (though longer) way of doing this.

                                     

                                    With V9 by default your times will, as you say, be read as HH:MM. 

                                     

                                    There is a CharToSeconds() function that will change the values to seconds BUT will be 60x the value you want. So take the calculation and divide by 60 to get the number of seconds.

                                     

                                    If you then place that value as a measure in a summary you can specify the format of the field as a 'TIME SPAN' field and set a subtotal line that would give you what you want.

                                     

                                    As far as I can see this should work fine with V7 as well but I may have overlooked something.

                                     

                                    HTH.

                                     

                                     

                                    Grant

                                     

                                    Edit to add: But bear in mind that Time Span fields will display as HHH:MM:SS and cannot be restricted to show minutes without hours afaik.