3 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Time in summaries

    Terry Murphy

      I wonder if anyone can help with this one:

      I have a field called TIMETOACCEPTED which comes from and XL file where it is formatted as TIME.

       

      In table view in Monarch it comes in as Character (greyed out).

       

      My question: Is there any way that this field (or a calulated field based on it) can be used to enable me to have an average time in minutes and seconds displayed in a summary?

       

      Sample:

      00:21

      00:21

      00:21

      00:21

      00:22

      00:22

      00:22

      00:22

      00:22

      00:22

      00:22

      00:22

       

      Hope someone can help

        • Time in summaries
          Grant Perkins

          Terry,

           

          The incoming field will be greyed out as Monarch cannot convert the type on the fly. So you need the calculated field which should be no problem BUT what do you see in Monarch?

           

          If I enter the numbers in the format you posted, whether the Excel field is set to Time or Text, monarch reads it as a Date (30/12/1899) and Time, from which you just want to extract a time field.

           

          If you don't get that what do you get?

           

          If I change the field FORMAT to TIME (from Date) I get 00:21:00 (for example) and I am guessing that your numbers are really 00:00:21 etc, so there will be some work to do perhaps though it may not matter to much if the response times don't exceed a day!

           

          You can convert everything to seconds, but how will depend on what you are seeing in the field.

           

          HTH.

           

          Grant

           

          Edit to correct some terminology.

           

          [size="1"][ January 24, 2007, 07:07 AM: Message edited by: Grant Perkins ][/size]

          • Time in summaries
            Terry Murphy

            Hi Grant;

             

            Thanks for the information, I have converted the time to seconds and averaged it in the summary with a format of 'Time Span' and it's looking good.

             

            Thanks again

             

            Terry

            • Time in summaries
              Grant Perkins

              Originally posted by Terry Murphy:

              Hi Grant;

               

              Thanks for the information, I have converted the time to seconds and averaged it in the summary with a format of 'Time Span' and it's looking good.

               

              Thanks again

               

              Terry /b[/quote]Hi Terry,

               

              That sounds good.

               

              The data I saw from an Excel field defined as TIME described the field as a decimal portion of a day (though still with the question about the hh:mm:ss structure.). Which is what I would expect at that point. So multiplying by an appropriate number (depending on what the fraction is really meant to be) would give the seconds. I guess that is what you have done?

               

              If the field was not an underlying 'fraction' of a day other Monarch functions would be required to convert the text to usable numbers.

               

              Anyway, it sounds like you have a usable result which is the most important thing.

               

              Grant