Calculate Hours Late for Employee

[Ashfaq]

Hello Experts,

I do have below data sets in my Source Database table.

The standard in and out times of employees are from 08:00 A.M till 02:00 P.M.

Emp	Date	Ref_IN_Time	IN_Time	Ref_OUT_Time	OUT_Time
101	5-Dec-18	8:00	8:50	14:00	12:10
102	5-Dec-18	8:00	9:00	14:00	13:30
103	5-Dec-18	8:00	10:45	14:00	11:30
104	5-Dec-18	8:00	8:00	14:00	14:00
105	5-Dec-18	8:00	8:00	14:00	14:00

for Example emp 101 is late for 50 Minutes in morning and he left early and he is late for 1 hour 50 Minutes in evening.

So in total ths employee is late for 2:40 2 hour and 40 Minutes for today.
In power BI I need to Calculate No of hours each employee he is absent in HH:MM

Emp	Date	Ref_IN_Time	IN_Time	Ref_OUT_Time	OUT_Time	Late	Status
101	5-Dec-18	8:00	8:50	14:00	12:10	2:40	Late
102	5-Dec-18	8:00	9:00	14:00	13:30	1:30	Late
103	5-Dec-18	8:00	10:45	14:00	11:30	2:45	Late
104	5-Dec-18	8:00	8:00	14:00	14:00	0:00	Ontime
105	5-Dec-18	8:00	8:00	14:00	14:00	0:00	Ontime

Now I need to Populate last 2 coulumns in power BI, Can somebody please provide there inputs on how to solve this.

[Greg_Deckler · Accepted solution]

I would do this in 3 columns:

Column = DATEDIFF([Ref_IN_Time],[IN_Time],MINUTE)+DATEDIFF([OUT_Time],[Ref_OUT_Time],MINUTE)


Status = IF([Column]=0,"On Time","Late")


Late = 
VAR __hours = INT([Column]/60)
VAR __minutes = [Column] - 60*__hours
RETURN CONCATENATE(CONCATENATE(__hours,":"),__minutes)

[Ashfaq]

Thank you @Greg_Deckler, It worked like a charm,

I do have one more question, But i will open another post for that.

Regards

ASHFAQ

[AlB]

Hi @Ashfaq

Employee 103 shouldn't be 2:45+ 2:30= 5:15 hours late?