Calculating Hours Between DateTime Stamp & Change Date

[DipeshVadgama]

Hi All,

I am trying to calculate total hour between each In & Out done by agents.

And also 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.

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD Name,

    Time,

    Date,

    [Date/Time],

    [IN/Out]

FROM

[..\..\Downloads\Book1.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

FinalTable:

LOAD *,

If(Name = Previous(Name),

If([IN/Out] = 'In' and Previous([IN/Out]) = 'Out',

Alt(Interval(Previous([Date/Time]) - [Date/Time], 'h:mm'), 'Out Miss'), 'Out Miss'), 'Out Miss') as [Total Hour],

Date(If(Time <= MakeTime(5) and [IN/Out] = 'In', Date - 1, Date)) as [Actual Login Date]

Resident Table

Order By Name, [Date/Time] desc;

DROP Table Table;

[DipeshVadgama]

Great Thank, This works. with above code if I also want to add what is a Max (Date & Time) and Min (Date & time) to actual login date in pivot or at loading. ???

[sunny_talwar]

You should be able to do this in the pivot table I think

[DipeshVadgama]

Hi,

I am using below expressions in pivots but it not giving me correct date & time

In load I have added below line

Date(If(Time <= MakeTime(5) and [IN/Out] = 'In', Date - 1, Date))+Time as [Actual Login Date/Time]

and in pivot I have added below expressions

min([Actual Login Date/Time])

max([Actual Login Date/Time])

[sunny_talwar]

Why are you adding time to this? I am not sure what you are looking to get... can you update your Excel to explain what you are looking for

[DipeshVadgama]

I have added pivot for reference

[sunny_talwar]

Awesome, will check this in a little bit.... have to run for a meeting

[DipeshVadgama]

Hi Friends,

Appreciate if any one can help me from here please.

[sunny_talwar]

Like this