Reply
mohsin-raza
Helper III
Helper III
Partially syndicated - Outbound

Calculate time difference with respect to Date and given time in decimal format

Hello:

 

I tried to calculate tworking hours with the rest time given in decimal format with 1 = 60 min and .75=.75*60=45 min and again one emplyee starts work one day AM and another day with AM( mark with yellow colour). I got wrong result in my measure . Can some body help me to solve this problem?

here is the sample dataset:

 

Sample.jpg

 

 

Regards: 

1 ACCEPTED SOLUTION
mohsin-raza
Helper III
Helper III

Syndicated - Outbound

I tried again and find the solution like 

 

Step1: Calculated the time difference in times by using function DATEDIFF and VALUES

 

min = DATEDIFF(VALUES(Table[TimeReportStart]),VALUES(Table[TimeReportEnd]),MINUTE)
 
Step 2: Multiple Hours with 60 to calculate time in mintes
 
Rest = sum(Table[BreakHours])*60
 
Step3 : Find the difference of minutes
diff=[min]-[Rest]
 
Step 4 : Convert the times in min into hour:min with following formula 
Working Hours=
var hourNo=INT([diff]/60)
var minuteNO=MOD([diff],60)
return
FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")
 
 
 
Hope this will solve same types of problem 😊
 

View solution in original post

1 REPLY 1
mohsin-raza
Helper III
Helper III

Syndicated - Outbound

I tried again and find the solution like 

 

Step1: Calculated the time difference in times by using function DATEDIFF and VALUES

 

min = DATEDIFF(VALUES(Table[TimeReportStart]),VALUES(Table[TimeReportEnd]),MINUTE)
 
Step 2: Multiple Hours with 60 to calculate time in mintes
 
Rest = sum(Table[BreakHours])*60
 
Step3 : Find the difference of minutes
diff=[min]-[Rest]
 
Step 4 : Convert the times in min into hour:min with following formula 
Working Hours=
var hourNo=INT([diff]/60)
var minuteNO=MOD([diff],60)
return
FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")
 
 
 
Hope this will solve same types of problem 😊
 
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)