login logout time calculate

[rajasekar_o]

Hi team ,

i have Login logout data, need to calculate IN TIME and OUT TIME 
IN TIME  min of login time 
in time must  between 9:30.00 am to 8:30:00 pm 

out time
if the employee have logout time on that date the take max of logout time ,if ther is no logout time on that date the check nexday before 9:30:00am take that time is logout time other wise is empty 

 how to calculate intime and out time expected output

  i try but not come correctly

[rajasekar_o]

HI TEAM , i have employee login  data  and name of employee data 
need to calculate in time and out time , i try more time but its not working 

i

"I want to calculate the 'in time' and 'out time' for employees.
The 'in time' should be the maximum 'intime' recorded between 9:30 am and 2:00 pm on a given date. If there is no 'in time' recorded, it should be blank.

For 'out time', if an employee has recorded an 'out time' on a specific date, it should be the maximum 'out time' for that date. If there is no 'out time' recorded, check the next day. If an 'out time' is recorded before 6:00 am on the next day, use that as the 'out time'; otherwise, 'out time' should be blank."
i give the expected output

[Daniel29195]

@rajasekar_o 

check the solution here . 

i guess it is the same logic with some minor differences. 

https://community.fabric.microsoft.com/t5/Desktop/Payroll-Data/m-p/3657730

if you still have any problems, i will be more than happy to assist you 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos 👍🤠

[rajasekar_o]

Hi team ,

i have Login logout data, need to calculate IN TIME and OUT TIME 
IN TIME  min of login time 
in time must  between 9:30.00 am to 8:30:00 pm 

out time
if the employee have logout time on that date the take max of logout time ,if ther is no logout time on that date the check nexday before 9:30:00am take that time is logout time other wise is empty 

i need out put intime and out time  i try but not come correctly , how to calculate intime and out time

[Idrissshatila]

Hello @rajasekar_o ,

you need first to split the date time column into two columns date column and time column.

check the following link for this first step https://youtu.be/RR5XvbRwCxI?si=GC__37R_Cv_zKc0L

then unpivot the table so you could have it as the expected result 
check this out https://youtu.be/URAJ_aDiREE?si=WPHNf8S4zo14MmQ5

[rajasekar_o]

not working 

[Anonymous]

Hi @rajasekar_o ,

I made many attempts, you can check the pbix file.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[rajasekar_o]

its working only one employee if employee added is not working 

[Anonymous]

Hi @rajasekar_o ,

Just include the name as one of the conditions.Try the following expression:

IN = var _t = ADDCOLUMNS('Table',"AM",MINX(FILTER(ALL('Table'),IF([Measure]<=14.5,[Name]=EARLIER([Name])&&[datetime.1]=EARLIER([datetime.1])&&[type ]="IN",[Name]=EARLIER([Name])&&[datetime.1] -1=EARLIER([datetime.1])&&[type ]="IN")),[datetime.2]))
RETURN MINX(_t,[AM])

OUT = var _t = ADDCOLUMNS('Table',"AM",MAXX(FILTER(ALL('Table'),IF([Measure]<=14.5,[Name]=EARLIER([Name])&&[datetime.1]=EARLIER([datetime.1])&&[type ]="OUT",[Name]=EARLIER([Name])&&[datetime.1] -1=EARLIER([datetime.1])&&[type ]="OUT")),[datetime.2]))
RETURN MAXX(_t,[AM])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @rajasekar_o 

Can you please share sample data in Excel format

[rajasekar_o]

empname	datetime	type
Ram	20.11.2023,09:30:00	IN
Ram	20.11.2023,09:30:02	IN
Ram	20.11.2023,20:30:01	OUT
Ram	21.11.2023,9:30:02	IN
Ram	22.11.2023,2:30:02	OUT
Ram	22.11.2023,09:30:02	IN
Ram	22.11.2023,20:30:02	OUT
Ram	22.11.2023,20:50:00	OUT
Ram	23.11.2023,09:50:00	IN
Ram	24.11.2023,09:50:00	IN
Ram	24.11.2023,20:30:00	OUT
Ram	24.11.2023,20:50:22	OUT

data

[rajasekar_o]

https://docs.google.com/spreadsheets/d/1yldN1eqRbpbbIyJT9B49JBEBCPNdj7xF/edit?usp=sharing&ouid=11461...