9 Replies Latest reply: Sep 13, 2017 12:53 PM by Sunny Talwar

# Calculating Hours Between DateTime Stamp & Change Date

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.

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

May be this

Table:

Time,

Date,

[Date/Time],

[IN/Out]

FROM

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

FinalTable:

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;

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

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

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

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

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

Hi,

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

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

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

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

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

I have added pivot for reference

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

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

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

Like this

• ###### Re: Calculating Hours Between DateTime Stamp & Change Date

Hi Friends,

Appreciate if any one can help me from here please.