5 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Calculating Time Field

    MelissaM _

      Hello,

       

      Forgive me, I am VERY new to Monarch.  I am trying to take a time field and if it is between a certain time, give me a certain time.  For example:

       

      Entry Time

      07:54:54

      08:42:38

      08:54:20

      09:03:59

       

      So, from here, I want all times between 08:00:00 and 08:59:59 and put a label of 8:00:00 on it.. and for every other hour with the correct label.  So it would look like this:

       

      Entry Time

      07:54:54  |  07:00:00

      08:42:38  |  08:00:00

      08:54:20  |  08:00:00

      09:03:59  |  09:00:00

       

      I was thinking of a calulated field and I tried a few things, nothing turned out right.  How would I go about doing this?

        • Calculating Time Field
          Grant Perkins

          Melissa,

           

          Assuming you have version 8 Monarch AND that your Entry Time field properties make it a Date/Time field Type with Time format, the following formula would be one way to do what you want.

           

          SecondsToChar(Hour()*3600)

           

          Your calculated field will need to be defined as a Character type and General format for this forumal to work. I have assumed, to keep things simple, that a Character display will be all you need. If that is not correct or you are using a version of Monarch without the required functions for the formula above, let us know and we can seek other options.

          • Calculating Time Field
            MelissaM _

            Hello Grant and thanks for the reply!  I am actually using Monarch Version 5.00.  I'm sorry I didn't state this before.  I did, however, try your formula and it stopped in front on "Hour" and said "Invalid Term".  Any other ideas?

             

            Thanks soooooo much,

            Melissa

            • Calculating Time Field
              Grant Perkins

              Originally posted by MelissaM:

                 I am actually using Monarch Version 5.00.  /b[/quote]Melissa,

               

              Hmm, the previous formula definitely won't work as it uses functions and features introduced after V5.

               

              My PC with V5 installed died recently and I have not yet got it running again. However I don't think anything required for this problem changed much between V5 and V6 so here is a V6 solution which should work.

               

              As far as I can remember the TIME string value will be a CHARACTER field in a V5 interpretation, which makes life easier in many ways. This formula will give you a calculated character field displaying the hours with minutes and seconds set to zero but looking like a full time display. The original field being converted is named

               

              LSplit(,2,":",1)+":00:00"

               

              This tells Monarch to do the following:

               

              Working from the LEFT, split the CHARACTER STRING into 2 part using the first : and give select the part of the string before the split point.

               

              This gives the Hour value.

               

              Add on (the + used to CONCATENATE characters) the string of characters specified within the quotes (:00:00)

               

              Display the result in a character defined field and you should have what you need.

               

               

              Note:

               

              This will not work unless the original field is character based (not numeric, date, etc.). However the other field types can usually be converted to a character field if necessary, using other Monarch functions, as part of the formula.

               

               

              I hope this helps.

               

              Grant

               

              Edit for Typos ...

              • Calculating Time Field
                MelissaM _

                Hi Grant,

                 

                That worked perfect!  Thank you very much.  One more thing though...  now the person I am doing this work for wants it in half hour increments.  How do I go about that??  For example:

                 

                7:31:00  |  7:30:00

                8:57:00  |  8:30:00

                9:21:00  |  9:00:00

                 

                So, anything between :00 and :29 would be referred to :00.  and any time between :30 and :59 would be referred to as :30.  Get it?  Is there a way I can do that?

                 

                Thanks!!!  ~ Melissa ~

                • Calculating Time Field
                  Grant Perkins

                  Hi Melissa,

                   

                  Yup, you can do that with this formula:

                  [font="courier"]LSplit(,2,":",1)+

                   

                  if(val(lsplit(,3,":",2)) <30,":00",":30")+

                   

                  ":00"[/font][/quote]The only reason I have split it like that is to show it in 3 sections. If you use it you can join the sections together removing the spaces.

                   

                  The first line is the hours processing which is the same as the previous formula.

                   

                  The second sections - (which ideally should display all on one line once the message entry window is finished with) checks the value of the hours part of the string and defines whether to display 00 or 30.

                   

                  The third section is the same as the first formula. (If you prefer to make the formula easier to follow you coud create some intermediate calculated files - One for HOUR (the first section) and one for MINUTE (The formula in the second section. You couold then build them into a third calculated field which would have a formula something like

                   

                  HOUR":"MINUTE+":00"

                   

                  To explain the MINUTE formula.

                   

                  I use the LSPLIT function again and this time split the string into 3 sections so we can get a value for the minutes in part 2 only.

                  [font="courier"]lsplit(,3,":",2)[/font][/quote]Now we need to compare this to 30 to decide if the the value to display should be 00 or 30. To do that we need to convert the character string to a numeric value using the VAL()  function.

                   

                  [font="courier"]VAL(lsplit(,3,":",2)[/font][/quote]converts to numeric for the comparison and

                   

                  [font="courier"]VAL(lsplit(,3,":",2)<30[/font][/quote]Makes the comparison looking for a value less than 30.

                   

                  Putting this into an IF() function allows us to decide what to to do with the result of the comparison.

                   

                  In longhand:-

                   

                  IF the numeric value of the Minutes part of the time string is less than 30, make the value to be used (in CHARACTERS not numeric) ":00", ELSE make it ":30".

                   

                  If your co-worker changes their mind and wants 15 minute resolutions instead, you can do that in much the same way but you will need to include 2 extra checks of the minute value by 'Nesting'    IF() functions within the formula and checking the number of minutes against a RANGE of values. A little more complex but not too bad once you get the range check right. However, lets keep it simpler for now.

                   

                  Have fun.

                   

                  Grant