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

[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')

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

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

[DipeshVadgama]

Hi,

I tried but I am getting wrong results.

[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 asc ,[In/Out] desc;

drop table Data;

[Kushal_Chawda]

It will be better if you can share the sample data

[Kushal_Chawda]

or you can create the straight table

Dimension:

Name,Date,Time,Date/Time,In/Out

Expression:

=if([IN/Out]='In' and Below(total [IN/Out])='Out',Interval(Below(TOTAL [Date/Time])-[Date/Time],'hh:mm:ss'),

if([IN/Out]='Out' and Above(total [IN/Out])='In',Interval([Date/Time]-above(total [Date/Time]),'hh:mm:ss'),

if([IN/Out]='In' and Below(total [IN/Out])='In','Out is missing')))

Note: Sort all the dimension ascending with the same order as written

[DipeshVadgama]

Hi Please find example file.

[effinty2112 · Accepted solution]

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
A	10/04/2017	4/10/17 12:15 PM	In	OK
A	10/04/2017	4/10/17 01:32 PM	Out	OK	01:17
A	10/04/2017	4/10/17 02:48 PM	In	OK
A	10/04/2017	4/10/17 05:35 PM	Out	OK	02:47
A	10/04/2017	4/10/17 05:38 PM	In	OK
A	10/04/2017	4/10/17 05:44 PM	Out	OK	00:05
A	10/04/2017	4/10/17 05:45 PM	In	Time Out missing
B	10/04/2017	4/10/17 12:12 PM	In	OK
B	10/04/2017	4/10/17 12:47 PM	Out	OK	00:34
B	10/04/2017	4/10/17 04:33 PM	Out	Time In missing
B	14/04/2017	4/14/17 05:21 PM	In	OK
B	14/04/2017	4/14/17 06:14 PM	Out	OK	00:53
B	14/04/2017	4/14/17 06:18 PM	In	OK
B	14/04/2017	4/14/17 07:22 PM	Out	OK	01:04
B	14/04/2017	4/14/17 07:42 PM	In	OK
B	14/04/2017	4/14/17 08:54 PM	Out	OK	01:11
C	10/04/2017	4/10/17 12:15 PM	In	OK
C	10/04/2017	4/10/17 01:32 PM	Out	OK	01:17
C	10/04/2017	4/10/17 02:48 PM	In	OK
C	10/04/2017	4/10/17 05:35 PM	Out	OK	02:47
C	10/04/2017	4/10/17 05:38 PM	In	OK
C	10/04/2017	4/10/17 05:44 PM	Out	OK	00:05
C	10/04/2017	4/10/17 09:40 PM	Out	Time In missing
D	10/04/2017	4/10/17 04:44 PM	Out	Time In missing
D	10/04/2017	4/10/17 04:48 PM	In	OK
D	10/04/2017	4/10/17 05:05 PM	Out	OK	00:17
D	14/04/2017	4/14/17 05:21 PM	In	OK
D	14/04/2017	4/14/17 06:14 PM	Out	OK	00:53
D	14/04/2017	4/14/17 06:18 PM	In	Time Out missing

Regards

Andrew