9 Replies Latest reply: May 15, 2014 9:55 AM by Lynette _ RSS

    Time Span

    Bradl Vogl

      Hi,

      I'm in need of some help.

      I have the following report. I need a calculated field for elapsed time (End - Start time)

      How can I achieve this? I looked at time span although it doesn't seem applicable. I would like the result in minutes.

       

      [font="courier"]Assembly     Qty     Start                   End               B/U #     Assembler

      DC2847900     50     2006-03-07 8:16:08 AM     2006-03-07 11:54:42 AM     115127     Kim

      DC2847900     50     2006-03-07 8:19:42 AM     2006-03-07 11:55:42 AM     115127     Chris

      J02602-6     99     2006-03-07 8:27:17 AM     2006-03-07 12:00:00 PM     112783     Kathy

      J02602-6     99     2006-03-07 8:27:42 AM     2006-03-07 12:00:00 PM     112783     Sue  /font[/quote]Thanks, in advance, for your help

       

      Brad Vogl

        • Time Span
          Grant Perkins

          Nice solution Todd.

           

          I can never remember the number of seconds in a day  - maybe I would if I needed it often enough - so I tend to make the calculation in the 'long' form. And of course once you are happy with the concept and the result unless you need the separate fields for some reason the whole calculation can be set up as a single field.

           

          ((-[Start Time])(2460*60))/60

           

          Now, if you add some descriptive text to the formula for ease of remembering what it does later (or if someone else may have to understand it at some point) you could also truncate it to this;

           

          (-[Start Time])(2460)

           

          which will give the same answer in a field defined as Numeric and 'General' as Todd explained.

           

          Two observations related to the sample report. (Probably most relevant to future forum browsers...)

           

          Firstly you MUST INCLUDE the AM or PM indicators in the fields if the times are in 12hour format or the calculations will produce unexpected results.

           

          Secondly the sample has no leading zero's before the hours in the START column and the temptation would be to create a 21 char wide field whereas the END column is 22 chars. Both fields would need to be 22 chars I would imagine.

           

          I hope this is of some use.

           

          Grant

           

          Edits due to failure of proof reading! (Twice ...    )

           

          [size="1"][ March 08, 2006, 01:26 PM: Message edited by: Grant Perkins ][/size]

          • Time Span
            Bradl Vogl

            Thanks to both of you for solutions.

            It works great!

            I have another issue.

            The solution you provided allows me to determine assembly times of a particular item.

            What I need to do is consider multiple assemblers producing the same item.

            As extracted, each assembler may be clocked in to a common Assembly,B/U# and Qty.

            I need to add an additional summary/calculation:

             

            Find all records that are common with the three fields listed.

            In those instances find total sum of hours while maintaining the single record quantity.

             

            Does this make any sense? I hope so.

            Please let me know if you have ideas.

             

            Thanks,

            Brad

            • Time Span
              Grant Perkins

              Originally posted by BradVogl:

              I need to add an additional summary/calculation:

               

              Find all records that are common with the three fields listed.

              In those instances find total sum of hours while maintaining the single record quantity.

               

              Does this make any sense? I hope so.

              Please let me know if you have ideas.

               

              Thanks,

              Brad /b[/quote]Brad,

               

              That sounds like a job for a Monarch summary. Have you already looked into that?

               

              If so was there a reason that it did not seem to work for you?

               

               

              Grant

              • Time Span
                Bradl Vogl

                Hi Grant,

                    Using Summary Count function worked.

                    Exported, Lookup and calculate results.

                    Thanks.

                 

                    I need a suggestion for another issue.

                    Sample of present Monarch Table below.

                    I need to do the following:

                 

                    If Start time is 12:00:00 AM then needs to be 8:00:00 AM

                    If End time is 11:59:59 PM then needs to be

                4:30:00 PM

                    All other times stay the same.

                    I tried using the IF function but received operand errors.

                    The time has both the Date and Time.....the Date always remains the same....just the time needs to change.

                    Ideas?

                 

                Thanks again,

                Brad

                 

                 

                     [font="courier"]Assembly     Qty     UNIT TIME     Start     End     Assembler     ELAPSED TIME     B/U #     MINUTES     COMMONS

                157-089     40     1.05     2006-03-10 2:02:02 PM     2006-03-10 2:43:37 PM     Darcie     2495          42     1

                157-089     40     2.18     2006-03-10 10:32:52 AM     2006-03-10 12:00:00 PM     Leah     5228     115698     87     1

                157-089     40     17.25     2006-03-10 12:30:00 PM     2006-03-10 11:59:59 PM     Leah     41399     115698     690     1

                157-089     40     17.93     2006-03-11     2006-03-11 11:57:10 AM     Leah     43030     115698     717     1

                157-092     40     0.65     2006-03-10 3:39:02 PM     2006-03-10 4:05:13 PM     Brian     1571          26     1

                  /font[/quote]

                • Time Span
                  Bradl Vogl

                  Grant,

                      After I looked at the reply I realized I never explained what I'm trying to do.

                      When an employee forgets to clock out of a project the software presumes they worked through the night. It clocks them out at 11:59:59 PM and clocks them in at 12:00:00 AM.

                      Our work day is 8:00 am to 4:30 pm.

                      Prior to exporting the data I need to correct any clock-in or out errors. 

                   

                  Thanks again

                  Brad Vogl

                  • Time Span
                    Grant Perkins

                    Originally posted by BradVogl:

                         When an employee forgets to clock out of a project the software presumes they worked through the night. It clocks them out at 11:59:59 PM and clocks them in at 12:00:00 AM.

                        /b[/quote]Hmm.

                     

                    I have heard of a few people who made really good money and gained company awards for commitment by forgetting to clock off ...

                     

                    But in this cas I guess their productivity would not look too good!

                     

                    Do you realise you may be upsetting some of the workers Brad?      

                     

                     

                    Grant

                     

                    PS. Thanks for the explanation. I am very fond of the concept of looking for a by-product of model which can highlight potential error situations and simplify checking as well as models where such checks are the prime purpose.

                    • Time Span
                      Gareth Horton

                      Brad,

                       

                      You need something like this.  Note the curly braces around the specification of time values.  This is also required when specifying dates as constants.

                       

                      [font="courier"]if(Time(End_Time)=,Date(End_Time)+,End_Time)  /font[/quote]Do the same sort of thing for the start times.

                       

                      Gareth

                       

                      Originally posted by BradVogl:

                      Hi Grant,

                          Using Summary Count function worked.

                          Exported, Lookup and calculate results.

                          Thanks.

                       

                          I need a suggestion for another issue.

                          Sample of present Monarch Table below.

                          I need to do the following:

                       

                          If Start time is 12:00:00 AM then needs to be 8:00:00 AM

                          If End time is 11:59:59 PM then needs to be

                      4:30:00 PM

                          All other times stay the same.

                          I tried using the IF function but received operand errors.

                          The time has both the Date and Time.....the Date always remains the same....just the time needs to change.

                          Ideas?

                       

                      Thanks again,

                      Brad

                       

                       

                           [font="courier"]Assembly     Qty     UNIT TIME     Start     End     Assembler     ELAPSED TIME     B/U #     MINUTES     COMMONS

                      157-089     40     1.05     2006-03-10 2:02:02 PM     2006-03-10 2:43:37 PM     Darcie     2495          42     1

                      157-089     40     2.18     2006-03-10 10:32:52 AM     2006-03-10 12:00:00 PM     Leah     5228     115698     87     1

                      157-089     40     17.25     2006-03-10 12:30:00 PM     2006-03-10 11:59:59 PM     Leah     41399     115698     690     1

                      157-089     40     17.93     2006-03-11     2006-03-11 11:57:10 AM     Leah     43030     115698     717     1

                      157-092     40     0.65     2006-03-10 3:39:02 PM     2006-03-10 4:05:13 PM     Brian     1571          26     1

                        /font[/quote][/b][/quote]

                      • Time Span
                        Grant Perkins

                        Brad,

                         

                        Sorry, saw the explanation post and thought it referred to the earlier one - the question above it completely failed to register with me.    

                         

                        I blame the aging process.

                         

                        On the other hand the benefit is that you get a much more effective solution than I would have come up with I suspect.

                        • Time Span
                          Lynette _

                          Gareth,

                          Referring to your suggestion to Brad as quoted below, it works well when using and "=" operator.

                          Quote

                          if(Time(End_Time)=,Date(End_Time)+,End_Time) 

                          Unquote

                           

                          Why can I not adapt the above replacing the "=" with ">" so that if endtime in an example like the one above is greater than 11:59:59 I could ideally get it default to start time on the following day say 08:00:00 AM with a +1 added to the date(end_time).

                          Kindly advise/Lynette