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

# Calculating Hours Between Date & Time

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

• ###### Re: Calculating Hours Between Date & Time

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

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

Data:

Name,

Date,

Time,

[Date/Time],

[In/Out]

FROM data;

Final:

noconcatenate

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

Hi,

I tried but I am getting wrong results.

• ###### Re: Calculating Hours Between Date & Time

Data:

Name,

Date,

Time,

[Date/Time],

[In/Out]

FROM data;

Final:

noconcatenate

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;

• ###### Re: Calculating Hours Between Date & Time

It will be better if you can share the sample data

• ###### Re: Calculating Hours Between Date & Time

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

• ###### Re: Calculating Hours Between Date & Time

Hi Dipesh,

Try this script:

RawData:

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:

*,

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

Regards

Andrew

• ###### Re: Calculating Hours Between Date & Time

Thanks, This works perfect as needed.

• ###### Re: Calculating Hours Between Date & Time

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.

• ###### Re: Calculating Hours Between Date & Time

Hi Dipesh,

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

Cheers

Andrew

RawData:

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:

*,

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

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

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;