9 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    24 Hr Clock & Calculating Hours

    Bob Green

      :confused:I am new at this.  I have looked through the manual and also the help section of Monarch and cannot find how to convert times to a 24 hr clock, calculate where a time goes from PM time to AM time for second shift situations, and finally calculating the hours as elapsed time.

       

      Can you help?

        • 24 Hr Clock & Calculating Hours
          Grant Perkins

          Hi Bob and welcome to the forum.

           

          You know there is nothing like cutting your Monarch teeth on one of the more confusing aspects of data manipulation. It seems to be almost a requirement for new users of the software but you seem to have hit the top step with your need for time calcualtions - always an interesting challenge no matter what software is in use.

           

          To some extent the answers to your questions will depend on where your starting point is and how the input data is presenting.

           

          An early question here is whether or not you business systems already have the pre-calcualted values available? They may not be in the report your are working with but they may be elsewhere in another report that might have suitable ways to link the already calculated answers to data you need to work with. Given the potential complexity of date and time handling between systems with different core methods any source that candeliver pre-calculated answers is worth seeking. You might find that results are faster and more reliable because you are not re-inventing the wheel and the existing reports SHOULD be fully tested (and presumably fully supported) already.

           

          If you have to do your own calculations look at the Help information on the Time Span function, date format options and be mindful that most of the calculations relating to dates and times depend on field be being calcualted as parts of a day (decimal style) based on the number of seconds in a day.

           

          The format of the dates (and times) in your source report can also have an influence as can the format options you have set for Input Options and, potentially, the defaults set for diplay parameters within your computer's operating system for regional and language settings.

           

          It might be easiest, since you have already read the help information, if you could post some examples of what you need to do and let us provide you with some worked examples that give you (or get you close to ) the answers you are seeking.

           

          Would that be possible?

           

           

          Grant

            • 24 Hr Clock & Calculating Hours
              Bob Green

              Thank you for your reply, Grant

               

              START DATE     START TIME   STOP DATE     STOP TIME  ELPS HRS          

                 26-Jan                     5:40                26-Jan         15:41          10.02                  

                 26-Jan                     5:05                26-Jan          5:36           0.52                  

                 27-Jan                     5:37                27-Jan          6:25           0.80                  

                 30-Jan                     7:39                30-Jan          8:15               ?            

               

              ADJ START     ADJ STOP    NET HRS

                  5:40               7:45            2.08

                  5:05               5:36            0.52

                  5:37               6:25            0.22

                  8:00             8:15                0.08

               

              The Elapsed (ELPS) Hours column is one issue.  In attempting to calculate the difference, in the first row it doesn't look wrong.  I found that by changing the calculations to match the time format (in Excel) I could get an accurate figure for the variance per line.  I have not been fortunate to do the same in Monarch.  For me it is not clear how I can get a calculation that matches the results with Excel.  I would rather have it in Monarch so that it is done once and is locked in.

               

              Another issue is converting from a 12-hour clock to a 24-hour one.  I, again, have not found any info to do so.

               

              Third, I need to get a total of all the hours performed. So far I have found that when I try to get a total, it does not add them up.  In Excel I get a 4 character, 2-decimal answer instead of a 4 to 8 character total (including the 2-decimal numbers).

                • 24 Hr Clock & Calculating Hours
                  Grant Perkins

                  Bob,

                   

                  The data you posted woould presumably present like this?

                  START DATE START TIME STOP DATE STOP TIME ELPS HRS ADJ START ADJ STOP NET HRS

                  26-Jan     5:40       26-Jan    15:41     10.02    5:40      7:45      2.08

                  26-Jan     5:05       26-Jan     5:36      0.52    5:05      5:36      0.52

                  27-Jan     5:37       27-Jan     6:25      0.80    5:37      6:25      0.22

                  30-Jan     7:39       30-Jan     8:15         ?    8:00      8:15      0.08

                  /code

                   

                  Looks like the calcs are in decimal hours.

                   

                  I assume that ELPS is the difference between (start date +start time) and (Stop Date + Stop time) and Nets hours will be the difference between (start date + ADJ Start) and (stop date + ADJ Stop)

                   

                  So for your ELPS calc you need to work in seconds (effectively) to get to minutes rather than parts of an hour. I'll come back to the options.

                   

                  The 24hr clock question is, as a date and time, based on your default windows date and time display settings. However when you come to display a time only calculation (such as an elapsed time) there is no reference to the date and therefore display will be in time format something like HHH:MM:SS for example.

                   

                  So there are a coupld of approaches here - Monarch offers several related functions and it comes down to which ones are most suited to your needs. There are also a number of possible approaches to this.

                   

                  I have guessed, perhaps wrongly, that your requirements may include overnight shift patterns where the stand and end could be on different days. That suggests the most full proof apprach might be to convert everything to date and time values and calc from there.

                   

                  To do that we need to add a year ...

                   

                  In the examples I have simply created the date as based on the current year but you can set any date available to you from the report or by operator entry. It will not matter (other than for February dates in leap years) when calcuklating an elapsed time.

                   

                  We can make the Start Date and Time like this:

                   

                  ctod("-"str(year(today())))+[/code]

                   

                  We are starting with a string and want to end up with a date/time field displaying as a Short date and time.

                   

                  So concatenate your Day/month with "-" (for date formatting) and then a string for the YEAR part grabbed from the current date using the DATE() function.

                   

                  The STR() function is used to convert the extract year (from today's date in this case) from a numeric value to a character value to match the rest of the date extract format and allow concatenation prior to converting back to a proper date.

                   

                  CTOD function makes this a date format (CharacterTODate)

                   

                  Do the same with the Stop values:

                   

                  ctod("-"str(year(today())))+[/code]

                   

                   

                  If we had called those calculated fields

                  and

                   

                  The elapsed hours calculation becomes;

                  /SIZE

                  (-[Full Start Date and Time])*86400[/code]

                   

                  Where 86400 is the number of seconds in a day.

                   

                  The expanded form of this formula, avoiding intermediate fields, is therefore:

                   

                  ((ctod("-"str(year(today())))+)-

                  (ctod("-"str(year(today())))+))*86400[/code]

                   

                  In both cases this field needs to be set up as a NUMERIC field of TIME SPAN format.

                   

                  Recent versions of Monarch (V9 and V10) also have an AGE() function that could be used in this example.

                   

                  age((ctod("-"str(year(today())))+),

                  (ctod("-"str(year(today())))+),7)[/code]

                   

                  In a slightly more readable form:

                   

                  age(,[stop D&T],measure in seconds)

                   

                  The resulting number of seconds is re-displayed in HH:MM:SS format.

                   

                  If you measure in minutes you would get 00:10:01 (for example).

                   

                  Note that if the result exceeds 24 hours and you have selected minutes or seconds as the measure for the AGE() calculation the displayed values, formatted as  Time Span, may not appear as you expect them to.

                   

                  This is a starter suggestion. See where that takes you and let us know when the next questions arise.

                   

                  Personally I also found (and still find) that it can be useful to browse the help sections for all the relevant functions that have date and time connections (and get a confident feel for the related field definitions as well) in order to work with dates and times most effectively. It can all get a bit complex and confusing. (Very complex if Time Zones come into play ... thankfully they do not seem to be an issue here.)

                   

                  HTH.

                   

                   

                  Grant

                   

                   

                  /SIZE

                   

                  /SIZE[/SIZE][/SIZE][/SIZE]

                    • 24 Hr Clock & Calculating Hours
                      Bob Green

                      Grant,

                      In the first question on the presentation of the data, yes that is the way it appears.

                      The first part, the ELPS calc is correct.  The Net hours is the difference between the ADJ Start and ADJ Stop times. Due to subsequent adjustments made by our production control people, this should represent those adustments. My concern here is mostly the ability to calculate and total the hours.

                      There are shift patterns that do overlap from PM to AM. We have just changed from a 5-8hr days to 4-10hr days.  This may increase the number of jobs this will affect on second shift.

                      GEtting into the codes: I am a visual and often a literal interpreting person.  I see the Codes but am not clear in which menus I need to apply them. Whatever I do to the ELPS column I will also have to do to the Net Adj column.

                       

                      Assuming I am in the Model mode, would you please walk me through the menus. There are so many strings, I want to be sure I do the steps right.  This will be a major guide to any future endeavors of mine in this system.  What I do is write procedures that would have had Napoleon Bonaparte's aide able to understand it, so that even NB's generals would understand it.

                        • 24 Hr Clock & Calculating Hours
                          Bob Green

                          I have enclosed here a sample of the report heading and a line of detail as it comes out of Avante into Monarch.  Maybe this would be more of a help in my earlier request, and your coding.

                           

                                                                                    Tx              Op  Sh  Ind                 Start  Start  Stop   Stop       

                          Div Dept Emp No Employee Name   Tran ID Ty Work Ord Seq Cd Code Shift Date Date  Time  Date   Time   

                          --- -


                          -


                          -


                            -


                              -- -


                          ---   -- -


                          -


                            -


                            -


                          -


                            -


                                                                                                          01  0540 221    QUINTERO  F     1199354   2  53169V   420  1         01/26/2009 01/26 05:40 01/26 15:41  

                           

                          Elapsed  Adj   Adj   

                          Hours    Start Stop  Net Hrs                                                                               

                          -


                            -


                          -


                          -


                               

                          10.02  05:40    07:45    2.08

                            • 24 Hr Clock & Calculating Hours
                              Bob Green

                              I went to your examples, converted my separated Start Date and Start Time to one field; combined the formats to Start Date & Time, changed the Type to Date/Time, the format to Short Date & Time & expanded the template width to 22.

                               

                              I then went to the Analysis tab and entered the string you provide into the first string field.  I got a hypen for my efforts.  I reverted back to the original formats, separating the date from the time; at least until I hear back from you.

                              • 24 Hr Clock & Calculating Hours
                                Grant Perkins

                                I have enclosed here a sample of the report heading and a line of detail as it comes out of Avante into Monarch. Maybe this would be more of a help in my earlier request, and your coding.

                                 

                                Tx Op Sh Ind Start Start Stop Stop

                                Div Dept Emp No Employee Name Tran ID Ty Work Ord Seq Cd Code Shift Date Date Time Date Time

                                --- -


                                -


                                -


                                -


                                -- -


                                --- -- -


                                -


                                -


                                -


                                -


                                -


                                01 0540 221 QUINTERO F 1199354 2 53169V 420 1 01/26/2009 01/26 05:40 01/26 15:41

                                 

                                Elapsed Adj Adj

                                Hours Start Stop Net Hrs

                                -


                                -


                                -


                                -


                                 

                                10.02 05:40 07:45 2.08[/quote]

                                 

                                Bob,

                                 

                                Sorry, the edits seem to have messed with the format and I don't want to guess what's right here.

                                 

                                There is a feature in the forum that allows such things to be posted in a retained format. Can I refer you to [URL="http://www.monarchforums.com/showthread.php?t=2290"]this[/URL] description, I think it will help you and save time. The first post it then most relevent here.

                                 

                                Also my earlier example work was based on you needing to calculate things like the ELPS time but I see that you actually get this from the Avante system so I obviously misunderstood that calculations you require. (The basic concepts still hold value though.)

                                 

                                So if you get the ELPS and the Net Hrs (albeit in decimal hours rather than hours and minutes) what are the values you need for your calculations? Are you basing them on the Avante calculated hours but with some re-interpretation or are you going back to the reported dates and times and calculating afresh? Should I take it to mean that you do not need the total hours for individual lines (you already have them) but you do need them in a summary subtotal for all selected lines?

                                 

                                I'm not sure why your replication of my formula failed (I hope you created that in a new field for easy removal purposes.)

                                 

                                There may be something about data format recognition - bear in mind my system is set to UK date formates in Windows and for the input date options. The template date width for a date a time field need only be 8 (date and time default). The default short display width is 20 characters but this allows for AM and PM so will have some white space if displaying time  in 24hr format.

                                 

                                For the elapsed field result the default DISPLAY width of a NUMERIC field is also 20 and the data size is 18.

                                 

                                Since Avante is providing the calculations already I am wondering if the problem you have is that summing the times, as decimal hours, for multiple lines gives the wrong result. If so the solution you require will involve a couple of calculated fields to convert the reported HH.hh values to something that Monarch can better work with, do the calculation and then convert back again if you need the decimal hours rather than HH:MM.

                                 

                                HTH.

                                 

                                 

                                Grant

                      • 24 Hr Clock & Calculating Hours
                        Bob Green

                        I have copied a page of the data as it comes off the Monarch system. Below the word "Employees" begins the header information. When extended, the items will fall into place. In the detail, the second line is an extension of the first.  I only pick up the header info that begins with the work center (WC) and ends with the Work Order number.  The remainder comes from the PPH total line beginning with the Qty Cmplt.

                         

                        In addition to the time issue we have discussed, there are three other conditions that I believe need to be fixed in Avante so that Monarch can properly reflect the data. I don't think these can be manipulated by Monarch. They are the duplication of order headers when they overlap onto the next page; where two operators, usually from different shifts, or where the employee performs different operations, are captured on the same record. I've provided here samples of these items.

                         

                        Hopefully, you can make some sense of the item(s) to give me some better guidance.

                         

                        [SIZE="1"][/SIZE]

                         

                        Page overlap, duplication of header data, WO 217945.

                         

                        500A  02/13/2009 501    CONTRERAS  D    217945   515 1      18        0      0    1.00   1.00    0.00   0.00     0.00      0       0   

                                                AN818-8T                                                                               

                        500A  02/13/2009                        217945   515 1      18       24           0.00   0.00    0.69   1.21     0.92     20      26   

                                                AN818-8T                                                                               

                        500A  02/13/2009                        217945   515 1      18        0      0    0.00   0.00    0.00   1.33     0.00      0       0   

                                                AN818-8T                                                                               

                        500A  02/13/2009                        217945   520 1      18        0      0    1.00   0.75    0.00   0.00     0.00      0       0   

                                                AN818-8T                                                                               

                        16 FEB 09 08:07AM                            Pieces per hour report - Work Center                                        Page 3        

                        SFCR9026.4 BGREEN B.Green                  Avante 9.5.4e AEROFIT AVANTE LIVE ACCOUNT                                                                               

                        Company  01   AEROFIT INC.                                                                               

                        Division  01   Department  ALL    Shift Code      ALL    WC  ALL                                      

                                                            Starting Date  02/13/2009  Ending Date  02/14/2009                                                                               

                        All Employees                                                                               

                        Op Sh             Qty    Qty     Std    Act     Std    Act  Std Hrs Rn pcs  St pcs   

                        WC   Shift Date Emp No Employee Name   Work Ord Seq Cd Wo Qty   Complt  scrap   setup  Setup   labor    Run Complete per Hr  per hr   

                        -


                        -


                        -


                        -


                        -


                        --- -- -


                        -


                        -


                        -


                        -


                        -


                        -


                        -


                        -


                        -


                             

                        500A  02/13/2009 501    CONTRERAS  D    217945   520 1      18       24           0.00   0.00    0.28   0.38     0.37     63      65   

                                                AN818-8T                                                                               

                        500A  02/13/2009                        217945   520 1      18        0      0    0.00   0.00    0.00   0.13     0.00      0       0   

                                                AN818-8T                                                                               

                        PPH for W/O---->  217945                       48      0    2.00   1.75    0.97   3.05     1.29     16      37   

                         

                        Multiple employees on different shifts calculating under the same record instead of separate records (EAR, C and Kamphan, C)

                         

                        500B  02/13/2009 926    EAR C           218549   520 2     189       27           0.00   0.00    8.97   1.85     1.28     15      21   

                                                31-310W120812                                                                               

                        500B  02/13/2009                        218549   520 2     189        0      0    0.00   0.00    0.00   0.85     0.00      0       0   

                                                31-310W120812                                                                               

                        500B  02/13/2009 679    KAMPHAN C       218549   520 1     189       92           0.00   0.00    8.97   1.36     4.37     68      21   

                                                31-310W120812                                                                               

                        500B  02/13/2009                        218549   520 1     189        0      0    0.00   0.00    0.00   1.00     0.00      0       0   

                                                31-310W120812                                                                               

                        500B  02/13/2009                        218549   520 1     189        0      0    0.00   0.00    0.00   1.28     0.00      0       0   

                                                31-310W120812                                                                               

                        PPH for W/O---->  218549                      119      0    0.00   0.00   17.94   6.34     5.65     19      21   

                         

                        Multiple operation sequences combined (515 & 520)

                                                                                              

                        500B  02/13/2009 505    NGUYEN V        218235   515 1      25        0      0    1.00   0.47    0.00   0.00     0.00      0       0   

                                                JSFF11W040404                                                                               

                        500B  02/13/2009                        218235   520 1      25        0      0    1.00   0.47    0.00   0.00     0.00      0       0   

                                                JSFF11W040404                                                                               

                        PPH for W/O---->  218235                        0      0    2.00   0.94    0.00   0.00     0.00      0       0

                          • 24 Hr Clock & Calculating Hours
                            Grant Perkins

                            Bob,

                             

                            Sorry, the report sample does not reformat successfully when copied and pasted into any application I use.

                             

                            Could you try it again using the CODE tags (or the # symbol from the edit command menu above the text window). That should allow you to retain the format and produce a scrolling 'box' from which I can successfully cut and paste. (It works 9 times out of 10.)

                             

                            I don't trust the accuracy of the result if I try to reformat the text manually. Nor do I have the time right now.

                             

                            Also, which columns are you trying to sum? And by what criteria? I might guess and be wrong.

                             

                            I assume your question re. duplication of header detail will be clearer with a correctly formatted report - I am concerned that it does not look totally evident right now as to where the problem lies.

                             

                            When you have two (or multiple?) operators on separate shifts/operations what sort of analysis breakdown are you looking for?

                             

                            I would suggest that you can likely get:

                             

                            By operator

                            By Shift

                            Bt Operation

                             

                            or even combinations of these from the same report but maybe not always in the same extraction. Not sure yet. I will try to experiment once I have a way to see the complete format.

                             

                             

                            Grant