Calculating Hours Between Date & Time

[DipeshVadgama]

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.

Name	Time	Date	Date/Time	IN/Out	Total Hour
A	12:15 PM	10-Apr-17	4/10/17 12:15 PM	In	1:17
A	1:32 PM	10-Apr-17	4/10/17 1:32 PM	Out
A	2:48 PM	10-Apr-17	4/10/17 2:48 PM	In	2:47
A	5:35 PM	10-Apr-17	4/10/17 5:35 PM	Out
A	5:38 PM	10-Apr-17	4/10/17 5:38 PM	In	0:05
A	5:44 PM	10-Apr-17	4/10/17 5:44 PM	Out
A	5:45 PM	10-Apr-17	4/10/17 5:45 PM	In	Error In or Out is missing
B	5:21 PM	14-Apr-17	4/14/17 5:21 PM	In	0:53
B	6:14 PM	14-Apr-17	4/14/17 6:14 PM	Out
B	6:18 PM	14-Apr-17	4/14/17 6:18 PM	In	1:04
B	7:22 PM	14-Apr-17	4/14/17 7:22 PM	Out
B	7:42 PM	14-Apr-17	4/14/17 7:42 PM	In	1:11
B	8:54 PM	14-Apr-17	4/14/17 8:54 PM	Out
B	12:12 PM	10-Apr-17	4/10/17 12:12 PM	In	0:34
B	12:47 PM	10-Apr-17	4/10/17 12:47 PM	Out	Error In or Out is missing
B	4:33 PM	10-Apr-17	4/10/17 4:33 PM	Out	Error In or Out is missing
C	12:15 PM	10-Apr-17	4/10/17 12:15 PM	In	1:17
C	1:32 PM	10-Apr-17	4/10/17 1:32 PM	Out
C	2:48 PM	10-Apr-17	4/10/17 2:48 PM	In	2:47
C	5:32 PM	10-Apr-17	4/10/17 5:35 PM	Out
C	5:38 PM	10-Apr-17	4/10/17 5:38 PM	In	0:05
C	5:44 PM	10-Apr-17	4/10/17 5:44 PM	Out	Error In or Out is missing
C	9:40 PM	10-Apr-17	4/10/17 9:40 PM	Out	Error In or Out is missing
D	5:21 PM	14-Apr-17	4/14/17 5:21 PM	In	0:53
D	6:14 PM	14-Apr-17	4/14/17 6:14 PM	Out
D	6:18 PM	14-Apr-17	4/14/17 6:18 PM	In	22:26
D	4:44 PM	10-Apr-17	4/10/17 4:44 PM	Out
D	4:48 PM	10-Apr-17	4/10/17 4:48 PM	In	0:17
D	5:05 PM	10-Apr-17	4/10/17 5:05 PM	Out

[DipeshVadgama]

Thanks, This works perfect as needed.

[DipeshVadgama]

Hi,

Thanks for the help, I am again stuck. need a help again.

I am try to add Actual Login Date. If any agent login between 12.00 AM to 5.00 AM then date should be  minus 1 day else same date. I have attached demo file hope that will help.

[effinty2112]

Hi Dipesh,

I've added a couple of lines to the script shown in red.

Cheers

Andrew

RawData:

LOAD Name,

     Time,

     Date,

    Date(Floor(Timestamp([Date/Time], 'M/D/YY hh:mm TT')-5/24)) as [Actual Login 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('ExcelRow',RecNo()-1,'SortedData') as ExcelRow,

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

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

Peek('Actual Login Date',RecNo()-2,'SortedData') as  [Actual Login 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;

[DipeshVadgama]

Thanks 🙂

[DipeshVadgama]

Thanks Andrew,

Appreciate if could able to help me similar problem.

Calculating Hours Between DateTime Stamp & Change Date