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

    Military Time

    Sandy McQuay

      Using Monarch Pro 6.

       

      I have an input field 12:03 am/5:03 pm.  How can I get that in military time so my export will contain 00:03 or 17:03?  I've tried several different function combinations -- splitting and converting fields, but haven't had any luck so far.  TIA

       

      Sandy McQuay

       

      [size="1"][ September 26, 2002, 04:01 PM: Message edited by: smcquay ][/size]

        • Military Time
          Steve Caiels

          Sandy,

           

          If you have all the time in one character field called civiltime, in the same format as your email example then try this.

           

          right("0"+

          if(right(civiltime,2)="pm",ltrim(str(val(left(civiltime,2))+12)),

          if(val(left(civiltime,2))=12,"00",lsplit(civiltime,2,":",1)

          )),2)

          ":"

          Extract(Civiltime,":"," ")

           

          It looks at the last 2 characters, if it is pm, then it converts the hours to a number, adds 12 and converts it back to a character again, then adds the colon and the minutes which are between the : and space.

          If the last two characters are am, then it checks to see if the hour is 12.  If it is it uses 0, if not then it uses whatever the hours are.  The right(xxx,2) at the beginning, adds the leading zero.

           

          An alternative approach would be to capture the hours minutes and am/pm as three separate fields (maybe using the floating trap), then the formulae would be easier.  But I'd need to know more about the report layout to help on that one.

           

          Regards

          Steve

          ps.  This is where I get embarrassed and somebody says "why not just use the xyz command"!!!!   :rolleyes:

          • Military Time
            Sandy McQuay

            Thanks, Steve.  I was able to take the code you posted, make a few modifcations, and get the results I needed.  The only thing I found is that for hours of 12 pm, I was getting 24:03 instead of 12:03.

             

            I also gave the wrong time format when I first posted.  It's actually 12:03am/5:03pm (no space before am/pm). Here's the code for the calculated field that I got to work:

             

            Right("0"If(Right(,2)="pm" .And. Left(,2)="12",Left(,2),If(Right(ArriveTm,2)="pm",LTrim(Str(Val(Left(ArriveTm,2))12,2,0)),If(Left(ArriveTm,2)="12","00",LSplit(ArriveTm,2,":",1)))),2)":"Subst r(LSplit(ArriveTm,2,":",2),1,2)

             

            The first If statement takes into account 12pm.

             

            Before I read your reply, here's what I had come up with:

             

            If(IsEmpty(TriTime),"",If(LSplit(TriTime,2,":",1)="12" .And. Substr(LSplit(TriTime,2,":",2),3,2)="am","00",If(LSplit(TriTime,2,":",1)="1" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","13",If(LSplit(TriTime,2,":",1)="2" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","14",If(LSplit(TriTime,2,":",1)="3" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","15",If(LSplit(TriTime,2,":",1)="4" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","16",If(LSplit(TriTime,2,":",1)="5" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","17",If(LSplit(TriTime,2,":",1)="6" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","18",If(LSplit(TriTime,2,":",1)="7" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","19",If(LSplit(TriTime,2,":",1)="8" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","20",If(LSplit(TriTime,2,":",1)="9" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","21",If(LSplit(TriTime,2,":",2)="10" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","22",If(LSplit(TriTime,2,":",1)="11" .And. Substr(LSplit(TriTime,2,":",2),3,2)="pm","23",If(2-Len(LSplit(TriTime,2,":",1))>0,"0"LSplit(TriTime,2,":",1),LSplit(TriTime,2,":",1)))))))))))))))If(IsEmpty(TriTime)=1,"",":") +Substr(LSplit(TriTime,2,":",2),1,2)

             

            Yours is much more compact!  [img]smile.gif[/img] 

             

            Now that I think about it, we didn't take into account if the time field is empty.  I'll look at that and repost the code when I come up with something.

             

            Again, thanks.  You pointed me in the right direction.

            • Military Time
              Sandy McQuay

              Here's the code for the calculated field that accounts for the field being empty:

               

              If(IsEmpty(ArriveTm)=1,"",Right("0"If(Right(,2)="pm" .And. Left(,2)="12",Left(,2),If(Right(ArriveTm,2)="pm",LTrim(Str(Val(Left(ArriveTm,2))12,2,0)),If(Left(ArriveTm,2)="12","00",LSplit(ArriveTm,2,":",1)))),2)":"Subst r(LSplit(ArriveTm,2,":",2),1,2))

              • Military Time
                Nick Osdale-Popa

                Here's what I worked out, not sure if it's "better" or just "different"    

                [font="courier"]IF(IsEmpty(ArriveTm)=1,"",

                IF(Right(ArriveTm,2) = "pm"  .And.    Val(Extract(ArriveTm,"",":"))<12,

                      Str(Val(Extract(ArriveTm,"",":"))+12,2,0),

                      If(Right(ArriveTm,2) = "am"  .And. Val(Extract(ArriveTm,"",":"))=12,"00",

                If(Val(Extract(ArriveTm,"",":"))<10,"0","")+

                             Extract(ArriveTm,"",":")))":"

                             Left(Extract(ArriveTm,":","m"),2))[/font][/quote]

                 

                [size="1"][ September 27, 2002, 04:55 PM: Message edited by: Nick Osdale-Popa ][/size]

                • Military Time
                  Mike Urbonas

                  Sandy and all,

                   

                  Monarch V6 can make this whole project MUCH easier, and, I believe, with NO complex formulas necessary.

                   

                  Begin with the following report trap to capture the time in a few useful separate fields (below is intended to be the portion of the Report window in Monarch, where you are defining your trap:

                   

                  [font="courier"]Trap  >>       :ÑÑ

                  Sample>>     12:03am[/font][/quote]AND, if the times do not line up from row to row (ie, 12:03pm versus one-digit hour times like 5:03pm), check the "Floating Trap" box ON, and the trap will still work.  This is a very useful new Monarch V6 feature.

                   

                  Now, create three separate fields:

                  • Two spaces to left of ":" - call it: HOUR

                  • Two spaces to right of ":" - call it MINUTES

                  • The next two spaces - call it: amORpm

                   

                  See how the Report trap is used to do a lot of the "heavy lifting" for us already?

                   

                  Next, in the table view, I recommend utilizing the new Monarch V6 "Lookup" calculated field type, to build a simple lookup table to convert 12-hour time to 24-hour military time. 

                  (1) First, create a simple Character calc. field simply to capture the hour followed by "am" or "pm".  We'll call this HourOfDay. Here is the formula:

                   

                  HOUR+amORpm

                   

                  which should yield a result such as:

                   

                  12pm

                  or:

                  5pm

                   

                  etc.

                   

                  (2) Next, using Monarch V6, create a new "Lookup" calculated field.  V6 will ask you what kind of calc. field you want to create:

                   

                   

                  Give the Lookup calc.field a name; we'll call this MilitaryHour.  Specify it will be a Character field.  Then, specify that HourOfDay is the "Input field."

                   

                  Now, you are allowed to enter an "Input Value" and the desired "Output Value" you want:

                   

                  [font="courier"]Input Value      Output Value

                  12am                  00

                  1am                   01

                  (etc)

                  12pm                  24[/font][/quote]The Lookup field handles the conversion of the HourOfDay field into the military hour without you having to any complex formulas with several IF statements, etc.

                   

                  (3) Finally, create a final calc. field called, for example, MilitaryTime:

                   

                  MilitaryHour+MINUTES

                   

                  Clearly the above-mentioned new V6 features will make your life MUCH easier.

                   

                  Hope this helps!!

                   

                  Regards

                  Mike

                   

                  [size="1"][ October 02, 2002, 11:59 AM: Message edited by: Mike Urbonas ][/size]

                  • Military Time
                    Nick Osdale-Popa

                    Oh, yeah, sure[/i] you could do it that[/i] way. 

                     

                    Just one caveat, this way is not easily portable to other reports, where a "complex" formula can be cut/pasted into another model. thppppppt.

                     

                    j/k

                    • Military Time
                      Mike Urbonas

                      Originally posted by Nick Osdale-Popa:

                      Oh, yeah, sure[/i] you could do it that[/i] way.      

                       

                      Just one caveat, this way is not easily portable to other reports, where a "complex" formula can be cut/pasted into another model. thppppppt.

                       

                      j/k[/b][/quote]Well, you know the saying..."Give a person a fish, they eat for a day..."    

                       

                      I do think it's important for those users who'd look at the above complex (but correct) formulas and run away      to see that Monarch V6 can do this kind of data "heavy lifting" for them!

                       

                      Regards

                      Mike

                       

                      PS - I did touch upon using the "Lookup" Calculated Field available in Monarch V6 in the April Moanrch Report Newsletter.  Check out the Monarch Tip at: [url="http://www.datawatch.com/pdf/products/monarch/Final_April_2002_Newsletter1.pdf"]http://www.datawatch.com/pdf/products/monarch/Final_April_2002_Newsletter1.pdf[/url]

                       

                      [size="1"][ October 02, 2002, 10:52 PM: Message edited by: Mike Urbonas ][/size]

                      • Military Time
                        Sandy McQuay

                        Cool . . . it works!  I just upgraded from V4 and have not looked at any of the new features.  I'll definitely tuck this one away for future reference!

                        • Military Time
                          Winn _

                          Originally posted by Mike Urbonas:

                          Begin with the following report trap to capture the time in a few useful separate fields (below is intended to be the portion of the Report window in Monarch, where you are defining your trap:

                           

                          [font="courier"]Trap  >>       :ÑÑ

                          Sample>>     12:03am[/font][/quote]AND, if the times do not line up from row to row (ie, 12:03pm versus one-digit hour times like 5:03pm), check the "Floating Trap" box ON, and the trap will still work.  This is a very useful new Monarch V6 feature.

                           

                          Now, create three separate fields:

                          • Two spaces to left of ":" - call it: HOUR

                          • Two spaces to right of ":" - call it MINUTES

                          • The next two spaces - call it: amORpm

                           

                          See how the Report trap is used to do a lot of the "heavy lifting" for us already?

                           

                          /b[/quote]If you set the Hour field as numeric, and the Minutes and amORpm fields as character then you can use this following formula to calculate the miliitary time:

                           

                          If(amORpm="pm",

                          Str(12Mod(Hour,12),2,0)Minutes,

                          Right("0"ltrim(Str(Mod(Hour,12),2,0)),2)Minutes)[/b]

                           

                          Alternatively, you can use this simpler (or more complex, depending on your point of view) formula:

                           

                          Right("0"LTrim(Str((amORpm="pm")*12Mod(Hour,12),2,0)),2)+Minutes[/b]

                           

                          These formulas have the advantage of using only one calc field, and no lookup table.

                          • Military Time
                            Mike Urbonas

                            Originally posted by Winn:

                            /size[quote]quote:[/size]Originally posted by Mike Urbonas:

                            Begin with the following report trap to capture the time in a few useful separate fields (below is intended to be the portion of the Report window in Monarch, where you are defining your trap:

                             

                            [font="courier"]Trap  >>       :ÑÑ

                            Sample>>     12:03am[/font][/quote]AND, if the times do not line up from row to row (ie, 12:03pm versus one-digit hour times like 5:03pm), check the "Floating Trap" box ON, and the trap will still work.  This is a very useful new Monarch V6 feature.

                             

                            Now, create three separate fields:

                            • Two spaces to left of ":" - call it: HOUR

                            • Two spaces to right of ":" - call it MINUTES

                            • The next two spaces - call it: amORpm

                             

                            See how the Report trap is used to do a lot of the "heavy lifting" for us already?

                             

                            /b[/quote]If you set the Hour field as numeric, and the Minutes and amORpm fields as character then you can use this following formula to calculate the miliitary time:

                             

                            If(amORpm="pm",

                            Str(12Mod(Hour,12),2,0)Minutes,

                            Right("0"ltrim(Str(Mod(Hour,12),2,0)),2)Minutes)[/b]

                             

                            Alternatively, you can use this simpler (or more complex, depending on your point of view) formula:

                             

                            Right("0"LTrim(Str((amORpm="pm")*12Mod(Hour,12),2,0)),2)+Minutes[/b]

                             

                            These formulas have the advantage of using only one calc field, and no lookup table.[/b][/size][/QUOTE][b]Ding! Ding! Ding![/b] We have a winner!  Give that person a 5 star rating!  Well done Winn (break into audience applause)  

                            • Military Time
                              Grant Perkins

                              Am I missing something here? Monarch 5 and 6 (can't remember if it was in 4 ) have 2 functions which should get the value required as follows (once you have identified the time field - called TARGETFIELD below) from the report by whatever method necessary in the trap. Include the AM or PM identifier in the field. e.g. '10:15 AM' or '10:15 PM' in order to get the correct results.

                               

                              Create a calculated field with the following formula;

                               

                              SecondsToChar(CharToSeconds(TARGETFIELD))

                               

                              Should give you all you need. Format to 5 chars wide for HH:MM or 8 chars for HH:MM:SS .

                               

                              Works on my system when I test it. Please let me know if it fails on yours.

                               

                              Best regards,

                               

                              Grant

                               

                                Originally posted by Grant Perkins:

                                /size[quote]quote:[/size]Originally posted by Mike Urbonas:

                                 /size[quote]quote:[/size]Originally posted by Winn:

                                  /size[quote]quote:[/size]Originally posted by Mike Urbonas:

                              Begin with the following report trap to capture the time in a few useful separate fields (below is intended to be the portion of the Report window in Monarch, where you are defining your trap:

                               

                              [font="courier"]Trap  >>       :ÑÑ

                              Sample>>     12:03am[/font][/quote]AND, if the times do not line up from row to row (ie, 12:03pm versus one-digit hour times like 5:03pm), check the "Floating Trap" box ON, and the trap will still work.  This is a very useful new Monarch V6 feature.

                               

                              Now, create three separate fields:

                              • Two spaces to left of ":" - call it: HOUR

                              • Two spaces to right of ":" - call it MINUTES

                              • The next two spaces - call it: amORpm

                               

                              See how the Report trap is used to do a lot of the "heavy lifting" for us already?

                               

                              /b[/quote]If you set the Hour field as numeric, and the Minutes and amORpm fields as character then you can use this following formula to calculate the miliitary time:

                               

                              If(amORpm="pm",

                              Str(12Mod(Hour,12),2,0)Minutes,

                              Right("0"ltrim(Str(Mod(Hour,12),2,0)),2)Minutes)[/b]

                               

                              Alternatively, you can use this simpler (or more complex, depending on your point of view) formula:

                               

                              Right("0"LTrim(Str((amORpm="pm")*12Mod(Hour,12),2,0)),2)+Minutes[/b]

                               

                              These formulas have the advantage of using only one calc field, and no lookup table.[/b][/size][/QUOTE][b]Ding! Ding! Ding![/b] We have a winner!  Give that person a 5 star rating!  Well done Winn (break into audience applause)         /b[/size][/QUOTE][/b][/size][/QUOTE]

                               

                              [size="1"][ November 02, 2002, 06:07 PM: Message edited by: Grant Perkins ][/size]

                              • Military Time
                                Winn _

                                Originally posted by Grant Perkins:

                                Am I missing something here? Monarch 5 and 6 (can't remember if it was in 4 ) have 2 functions which should get the value required as follows (once you have identified the time field - called TARGETFIELD below) from the report by whatever method necessary in the trap. Include the AM or PM identifier in the field. e.g. '10:15 AM' or '10:15 PM' in order to get the correct results.

                                 

                                Create a calculated field with the following formula;

                                 

                                SecondsToChar(CharToSeconds(TARGETFIELD))

                                 

                                Should give you all you need. Format to 5 chars wide for HH:MM or 8 chars for HH:MM:SS .

                                 

                                Works on my system when I test it. Please let me know if it fails on yours.

                                 

                                Best regards,

                                 

                                Grant[/b][/quote]This approach will only work if your PC system time is set to the HH:mm:ss format. The other formulas will work all the time.

                                • Military Time
                                  Grant Perkins

                                  OK Winn, good point.

                                   

                                  I never set anything other than the 24hr clock so I did not think of that as an issue. I will from now!

                                   

                                  Anyone not having concerns about clock display settings should be able to use the formula in my original post. Winn's solution is probably the simplest 'covers all' approach.

                                   

                                  However it does rely on a function in Monarch 6 (to which I would recommend everyone to move since it offer so many benefits)so I thought I would seek a solution for V5 use. Also I wanted to see how far we could get using the time converted to seconds since there are equivalent functions in most time calculations (Excel, et al.) and it might be useful to be able to convert to seconds for export purposes for example and use the same intermediate field for the Military Time display as well. In addition there may be times when imported data is more readily available as the 'seconds' value, in which case parts of the formula below could be used to convert that back to display military time. (24 hour clock in European terminology).

                                   

                                  Try this one if you are using Monarch 5 (where the MOD function used by Winn is, I believe, not available though of course you could create the equivalent in the formula).

                                   

                                  left(right("0"ltrim(str((CharToSeconds()/3600),5,2)),5),2)":"+(left(lsplit(,2,":",2),2))

                                   

                                  Explanation.

                                   

                                  If, in the template, we identify a time field on the report with the format HH:MM xM  (where XM represents AM or PM) we can treat this as a single field for the purposes of manipulating time formats for data extraction.

                                   

                                  (Note that different selected formats might lead to different ways to extract the data but the principle can remain the same. For example we might have a format including seconds HH:MM:SS xM. In which case, assuming we are not interested in the seconds, the method of identifying the MINUTES offers a simpler solution).

                                   

                                  We can treat this string as an identifier for the number of seconds elapsed in the day using the CharToSeconds function. This has equivalents in other progams, such as Excel, and so may sometimes offer a dual purpose solution if there is a need to exchange information for calculating time fields, for instance elapsed time.

                                   

                                  Obtaining the HOURS value

                                   

                                  Assuming we have named our field with a valid time format such as HH:MM XM then

                                   

                                  CharToSeconds()

                                   

                                  will convert this to number of seconds elapsed since 00:00.

                                   

                                  (CharToSeconds()/3600)

                                   

                                  Will give us a value for DECIMAL hours.

                                   

                                  From this we want to identify ONLY the hours figure. So 'define' the field as 5 characters long with 2 decimal places and must be a CHARACTER field. Use the STR function for this.

                                   

                                  (str((CharToSeconds()/3600),5,2)

                                   

                                  This would return '10.25' from a time value of 10:15 AM.

                                   

                                  We can separate the hours only with the following:

                                   

                                  ltrim(str((CharToSeconds()/3600),5,2))

                                   

                                  However there is a slight problem for single digit hours since the earlier numeric calculation will have omitted a leading zero which we need for correct military time display. The CHARACTER field we have created will have a leading space which we need to change to a zero. We can add that back as follows (and as Winn identified)

                                   

                                  "0"+ltrim(str((CharToSeconds()/3600),5,2))

                                   

                                  LTRIM will remove the leading space so we would have the CHARACTER field showing

                                   

                                  1.25 for 1:15 AM

                                  10.25 for 10:15 AM

                                   

                                  "0"+ adds a zero as the first character of the new 'field', so we would get

                                   

                                  01.25 for 1:15 AM

                                  010.25 for 10:15 AM

                                   

                                  If we now 'describe' this as a field of 5 CHARACTERs those being the last 5 characters of our calculated field we would get the following:

                                   

                                  right("0"+ltrim(str((CharToSeconds()/3600),5,2)),5)

                                   

                                  which will give us

                                   

                                  01.25 for 1:15 AM

                                  10.25 for 10:15 AM

                                   

                                  For the last step we want to select ONLY THE HOURS from the CHARACTER field.

                                   

                                  left(right("0"+ltrim(str((CharToSeconds()/3600),5,2)),5),2)

                                   

                                  will do this for us by selecting only the 2 leftmost characters from the intermediate CHARACTER string.

                                   

                                  That's the complex bit and mostly involves manipulating numeric calculations to CHARACTER fields and then 'formatting' the virtual field and selecting the parts of the data required.

                                   

                                  The MINUTES

                                   

                                  Now we have the HOURS we need to display the minutes and then format the whole output field.

                                   

                                  To get the minutes is relatively simple. However the most economical method may vary depending upon the original format on the source report. Using the format above

                                   

                                  10:15 AM

                                   

                                  we can drop the hours with the following:

                                   

                                  lsplit(,2,":",2)

                                   

                                  This will gives us "15 AM" 

                                   

                                  (If the Time format had been HH:MM:SS xM we could get exactly what was required with the  formula lsplit(,3,":",2 which would be a little simpler).

                                   

                                  Now we want just the first 2 CHARACTERS of our intermediate data.

                                   

                                  left(lsplit(,2,":",2),2) does that for us.

                                   

                                  The COMPLETE OUTPUT

                                   

                                  Finally we need to set the output format for the extracted data by stringing the fields together and perhaps inserting a ":" between them. (or any other character should you prefer! If you don't want the separator just leave out the '+":"' part. I note that Winn's formula omits the separator which I assume is correct for military time but would be unusual for 24hour time. Standards around the world may vary.)

                                   

                                  ":" will do that which, gives the following complete formula

                                   

                                  left(right("0"ltrim(str((CharToSeconds()/3600),5,2)),5),2)":"+(left(lsplit(,2,":",2),2))

                                   

                                  As ever there are several other approaches to resolving this sort of issue including, for example, creating a field in the template for the minutes (as they will not require re-calculation) and simply appending that to the hours manipulation during the final output.

                                   

                                  Now, anyone want to take this a stage further and convert to local timezone values as well?

                                   

                                  Best regards,

                                   

                                  Grant

                                   

                                  PS - If any of the above fails to work please let me know. I may have lost something in the transcription somewhere ....

                                   

                                  Originally posted by Winn:

                                  /size[quote]quote:[/size]Originally posted by Grant Perkins:

                                  Am I missing something here? Monarch 5 and 6 (can't remember if it was in 4 ) have 2 functions which should get the value required as follows (once you have identified the time field - called TARGETFIELD below) from the report by whatever method necessary in the trap. Include the AM or PM identifier in the field. e.g. '10:15 AM' or '10:15 PM' in order to get the correct results.

                                   

                                  Create a calculated field with the following formula;

                                   

                                  SecondsToChar(CharToSeconds(TARGETFIELD))

                                   

                                  Should give you all you need. Format to 5 chars wide for HH:MM or 8 chars for HH:MM:SS .

                                   

                                  Works on my system when I test it. Please let me know if it fails on yours.

                                   

                                  Best regards,

                                   

                                  Grant[/b][/quote]This approach will only work if your PC system time is set to the HH:mm:ss format. The other formulas will work all the time.[/b][/size][/QUOTE]