14 Replies Latest reply: Sep 13, 2017 10:59 AM by Dipesh Vadgama RSS

    Calculating Hours Between Date & Time

    Dipesh Vadgama

      Hi All,

       

      I am trying to calculate total hour between each In & Out done by agents, But I am unable to do it.

      Below is the example.

         

      NameTimeDateDate/TimeIN/OutTotal Hour
      A12:15 PM10-Apr-174/10/17 12:15 PMIn1:17
      A1:32 PM10-Apr-174/10/17 1:32 PMOut
      A2:48 PM10-Apr-174/10/17 2:48 PMIn2:47
      A5:35 PM10-Apr-174/10/17 5:35 PMOut
      A5:38 PM10-Apr-174/10/17 5:38 PMIn0:05
      A5:44 PM10-Apr-174/10/17 5:44 PMOut
      A5:45 PM10-Apr-174/10/17 5:45 PMInError In or Out is missing
      B5:21 PM14-Apr-174/14/17 5:21 PMIn0:53
      B6:14 PM14-Apr-174/14/17 6:14 PMOut
      B6:18 PM14-Apr-174/14/17 6:18 PMIn1:04
      B7:22 PM14-Apr-174/14/17 7:22 PMOut
      B7:42 PM14-Apr-174/14/17 7:42 PMIn1:11
      B8:54 PM14-Apr-174/14/17 8:54 PMOut
      B12:12 PM10-Apr-174/10/17 12:12 PMIn0:34
      B12:47 PM10-Apr-174/10/17 12:47 PMOutError In or Out is missing
      B4:33 PM10-Apr-174/10/17 4:33 PMOutError In or Out is missing
      C12:15 PM10-Apr-174/10/17 12:15 PMIn1:17
      C1:32 PM10-Apr-174/10/17 1:32 PMOut
      C2:48 PM10-Apr-174/10/17 2:48 PMIn2:47
      C5:32 PM10-Apr-174/10/17 5:35 PMOut
      C5:38 PM10-Apr-174/10/17 5:38 PMIn0:05
      C5:44 PM10-Apr-174/10/17 5:44 PMOutError In or Out is missing
      C9:40 PM10-Apr-174/10/17 9:40 PMOutError In or Out is missing
      D5:21 PM14-Apr-174/14/17 5:21 PMIn0:53
      D6:14 PM14-Apr-174/14/17 6:14 PMOut
      D6:18 PM14-Apr-174/14/17 6:18 PMIn22:26
      D4:44 PM10-Apr-174/10/17 4:44 PMOut
      D4:48 PM10-Apr-174/10/17 4:48 PMIn0:17
      D5:05 PM10-Apr-174/10/17 5:05 PMOut

       

        • Re: Calculating Hours Between Date & Time
          Anil Babu Samineni

          First work on Date Field

           

          Date(Date#([Date/Time],'MM/DD/YYYY hh:mm TT'),'DD-MM-YYYY HH:MM:SS TT') as [Date/Time]

           

          Then, For [Total Hours] you can use this?

          Interval(DateTime - Previous(DateTime),'hh:mm')

          • Re: Calculating Hours Between Date & Time
            Peter Cammaert

            What is it that your are unable to accomplish? Can you post what you already have?

             

            Also, it seems awkward that you use the In rows as terminal rows. What is the reason for attaching the period of time to the In row? And why do some Out rows carry an error message when there is a corresponding In row that reported a correct period? For example, see B row with time stamp 12:47PM.

            • Re: Calculating Hours Between Date & Time
              kushal chawda

              Data:

              LOAD

              Name,

              Date,

              Time,

              [Date/Time],

              [In/Out]

              FROM data;

               

              Final:

              noconcatenate

              LOAD *,

                        if([In/Out]='In' and previous([In/Out])='Out', interval(previous([Date/Time])-[Date/Time],'hh:mm'),'Out is Missing') as TotalHour

              Resident Data

              order by Name,Date,Time,[In/Out] desc;


              drop table Data;

              • Re: Calculating Hours Between Date & Time
                Dipesh Vadgama

                Hi,

                 

                I tried but I am getting wrong results.

                 

                • Re: Calculating Hours Between Date & Time
                  Dipesh Vadgama

                  Hi Please find example file.

                  • Re: Calculating Hours Between Date & Time
                    Andrew Walker

                    Hi Dipesh,

                    Try this script:

                    RawData:

                    LOAD Name,

                         Time,

                         Date,

                         Timestamp([Date/Time], 'M/D/YY hh:mm TT') as [Date/Time],

                         [IN/Out]

                    FROM

                    Book1.xlsx

                    (ooxml, embedded labels, header is 1 lines, table is Sheet1) Where Not IsNull(Name);

                     

                    NoConcatenate

                    SortedData:

                    Load * Resident RawData Order by Name, [Date/Time];


                    NoConcatenate

                    CheckedData:

                    LOAD

                    *,

                    If(Status = 'OK' and [IN/Out] = 'Out', Interval([Date/Time]-[Prev Date/Time],'hh:mm')) as [Total Hours];

                    LOAD

                    Peek('Name',RecNo()-1,'SortedData') as Name,

                    Peek('Date',RecNo()-1,'SortedData') as Date,

                    Peek('Date/Time',RecNo()-1,'SortedData') as  [Date/Time],

                    Peek('IN/Out',RecNo()-1,'SortedData') as  [IN/Out],

                    Peek('Date/Time',RecNo()-2,'SortedData') as  [Prev Date/Time],

                    if(Peek('IN/Out',RecNo()-1,'SortedData') = 'In',

                    if(Peek('IN/Out',RecNo(),'SortedData') ='Out' and Peek('Name',RecNo(),'SortedData') = Peek('Name',RecNo()-1,'SortedData'), 'OK', 'Time Out missing'),

                    if(Peek('IN/Out',RecNo()-2,'SortedData') ='In' and Peek('Name',RecNo()-1,'SortedData') = Peek('Name',RecNo()-2,'SortedData'), 'OK', 'Time In missing')) as Status

                    AutoGenerate NoOfRows('SortedData');

                     

                    Drop Tables RawData, SortedData;


                    To get:


                    Name Date Date/Time IN/Out Status Total Hours
                    A10/04/20174/10/17 12:15 PMInOK 
                    A10/04/20174/10/17 01:32 PMOutOK01:17
                    A10/04/20174/10/17 02:48 PMInOK 
                    A10/04/20174/10/17 05:35 PMOutOK02:47
                    A10/04/20174/10/17 05:38 PMInOK 
                    A10/04/20174/10/17 05:44 PMOutOK00:05
                    A10/04/20174/10/17 05:45 PMInTime Out missing 
                    B10/04/20174/10/17 12:12 PMInOK 
                    B10/04/20174/10/17 12:47 PMOutOK00:34
                    B10/04/20174/10/17 04:33 PMOutTime In missing 
                    B14/04/20174/14/17 05:21 PMInOK 
                    B14/04/20174/14/17 06:14 PMOutOK00:53
                    B14/04/20174/14/17 06:18 PMInOK 
                    B14/04/20174/14/17 07:22 PMOutOK01:04
                    B14/04/20174/14/17 07:42 PMInOK 
                    B14/04/20174/14/17 08:54 PMOutOK01:11
                    C10/04/20174/10/17 12:15 PMInOK 
                    C10/04/20174/10/17 01:32 PMOutOK01:17
                    C10/04/20174/10/17 02:48 PMInOK 
                    C10/04/20174/10/17 05:35 PMOutOK02:47
                    C10/04/20174/10/17 05:38 PMInOK 
                    C10/04/20174/10/17 05:44 PMOutOK00:05
                    C10/04/20174/10/17 09:40 PMOutTime In missing 
                    D10/04/20174/10/17 04:44 PMOutTime In missing 
                    D10/04/20174/10/17 04:48 PMInOK 
                    D10/04/20174/10/17 05:05 PMOutOK00:17
                    D14/04/20174/14/17 05:21 PMInOK 
                    D14/04/20174/14/17 06:14 PMOutOK00:53
                    D14/04/20174/14/17 06:18 PMInTime Out missing


                    Regards


                    Andrew