How to calculate duration in minutes between two columns

[nikhilmanohar]

Hello,

I have two columns from an attendance report that show both In Time and Out Time. I would like to know how to find the total duration (out time - in time). A simple subtraction gave me the result however I'm not able to get the sum of total duration. I'm learning Power BI / DAX and any help will be greatly appreciated.

In Time	Out Time	Duration
8:30:00 AM	3:03:00 PM	in HH:mm

[Drors]

First, change the type of the in time and out time columns to time format, it will change your data into 24 hours format.

after create new calculated column and use this dax formual

Duartion =
var IntimeMin = HOUR(Table1[In Time])*60+MINUTE(Table1[In Time])+SECOND(Table1[In Time])/60
var OutTimeMin = HOUR(Table1[Out Time])*60+MINUTE(Table1[Out Time])+SECOND(Table1[Out Time])/60 return

OutTimeMin-IntimeMin

[Anonymous]

Duartion =

var IntimeMin = HOUR(tg_task_allocation[InTime])*60+MINUTE(tg_task_allocation[InTime])+SECOND(tg_task_allocation[InTime])/60

var OutTimeMin = HOUR(tg_task_allocation[OutTime])*60+MINUTE(tg_task_allocation[OutTime])+SECOND(tg_task_allocation[OutTime])/60 return

OutTimeMin-IntimeMin

I have written this DAX for the following data ,but in my case it is showing me error .Please help me out.

[nikhilmanohar]

@Drors Thank you for the help. However, the output is in decimal number. Sorry to ask, how can I convert that to time format?

[Drors]

add another column with that dax function : 

Duartion Time Format = INT(Table1[Duartion]/60)&":" & MOD(Table1[Duartion],60)

and then in the modeling tab change the data type to time

[nikhilmanohar]

Since I have blanks in some of those In / Out time columns, it gives the following error.

'

[Drors]

Please share more accourate example of your data and I will fix the formula to them,

you can add "if" and "isblank" statments to the dax to prevent those errors..

[nikhilmanohar]

Here is what it is. Users might have punched in on both days or they might have missed either in punch or out punch for a particular day. 

In Time	Out Time	Duration
7:59:00	16:00:00	8:01
Blank	16:00:00	:
7:59:00	Blank	:

Please let me know if you need any additional information.

[Drors]

OK, So I change the duration to 0 if one of the "In Time" or "Out Time" is blank, you can change it to whatever you want..

change the Duatrion formula to : 

Duartion =
var IntimeMin = HOUR(Table1[In Time])*60+MINUTE(Table1[In Time])+SECOND(Table1[In Time])/60
var OutTimeMin = HOUR(Table1[Out Time])*60+MINUTE(Table1[Out Time])+SECOND(Table1[Out Time])/60 return
IF(OR(ISBLANK(Table1[In Time]),ISBLANK(Table1[Out Time])),0,OutTimeMin-IntimeMin)

Hope that this is what you want.. update me if not

[nikhilmanohar]

Thank you very much! Looks like there is one negative value due to which it cannot convert to time. Also, will changing to time format allow me to summarize by "Sum"? I would like to know the total duration of each employee in a week / month / year. 

[nikhilmanohar]

@Drors Everything is now perfect except that I cannot sum the values in the duration field. Can you help please?

[Drors]

I think it will be much easier to you to work with the duration in a float format for calculations like sum or avg..

time format shows time, for example, if the total duration of one of your employee is more then 24 hours, how do you want to see it in time format, 

in float format you will see it in minutes, or just divide it by 60 to see it in hours (for days divide it by 60*24)..

[nikhilmanohar]

hmm. I would like to have the total duration they have worked. Like 100 hours in a month. 

Found something from another post here. But it totals the result in days, hours, minutes and seconds. I would love to see this in hours, minutes format. Do you think I can get it in that format?

VAR TotalSeconds=SUMX('VIEW_DAILY_REPORT',HOUR('VIEW_DAILY_REPORT'[Duration])*3600+MINUTE('VIEW_DAILY_REPORT'[Duration])*60+SECOND('VIEW_DAILY_REPORT'[Duration]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

[Drors]

Im not sure if thats what you want but, you can make a "fake" time format and put it in card visualistion :

create new measure : 

Total Duration Time Format =
var totalduration = SUM(Table1[Duartion]) return
INT(totalduration/60) &":"& MOD(totalduration,60)

[nikhilmanohar]

hmm..that does not help 😞 

at this point, I do not know if displaying total hours is even possible.

Thank you @Drors for all your help. I truly appreciate it.

[v-yuezhe-msft]

@nikhilmanohar,

Drors's DAX works in my table. Could you please share the whole data of your table and post expected result here?

Regards,
Lydia

[nikhilmanohar]

@v-yuezhe-msft Dror's formula does work. But I need a caluculated column instead of a measure so that it will work accross filter contexts. My data now looks like this. The table has Date (2017 - 2018), Employee Number, In Time, Out Time & Duration. I need to figure out the total duration of an employee in HH:mm format. 

E.g. Total worked hours of Employee 1 for the month of April 2018 is 100.20 hours.

[v-yuezhe-msft]

@nikhilmanohar,

Create the following columns in your table.

Year = YEAR(Table[Date])

Month = MONTH(Table[Date])

Duartion = 
var IntimeMin = HOUR(Table[In Time])*60+MINUTE(Table[In Time])+SECOND(Table[In Time])/60
var OutTimeMin = HOUR(Table[Out Time])*60+MINUTE(Table[Out Time])+SECOND(Table[Out Time])/60 return
IF(OR(ISBLANK(Table[In Time]),ISBLANK(Table[Out Time])),0,OutTimeMin-IntimeMin)

hour = CALCULATE(SUM(Table[Duartion]),ALLSELECTED(Table[Month]),ALLSELECTED(Table[Employee]),ALLSELECTED(Table[Year]))/3600

Regards,
Lydia

[nikhilmanohar]

@v-yuezhe-msft Thank you. What data type to be used here? Cannot use time since it cannot summarize the values.

[v-yuezhe-msft · Accepted solution]

@nikhilmanohar,

Please check details in the PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1Nokzv42kpib9n_bF3j

Regards,
Lydia