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 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








              FROM data;




              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



                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:


                    LOAD Name,



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




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




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





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


                    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