Calculate Hours and Minutes and Sum Hours and Minutes

[sureshsonti]

My data has Employee Code, Punch In, Punch Out. I am trying to find the number of hours worked (Hours: Minutes) and calculate total hours worked in Hours:Minutes

Thanks

[sureshsonti]

I still get the sum of all hours worked by the employees.

[ChrisMendoza]

@sureshsonti,

I think you'll be better off sum-ing in tenths of an hour rather than trying to keep with a time format 00:00. How would the computer visualize a duration greater than 24 hours in time format? Since datetime is a visual format that only represents a decimal number i suggest you change to decimal format as:

SimpleDuration (hrs in tenths) = 
VAR SimpleDurationMins = 
    DATEDIFF('Table'[Punch In],'Table'[Punch Out],MINUTE)
    
RETURN DIVIDE(SimpleDurationMins,60) 

[MitchM]

Another option is to create a new column:

Time Worked = Sheet1[Punch Out] - Sheet1[Punch In] (i was using excel)

This will calculate the time worked. Make sure you change the data type to:

Modeling --> Date Type --> Date/Time --> 13:30(H:mm) 

You can then create a measure to calculate the sum:

Total Time worked = CALCULATE(SUM(Sheet1[Time Worked]))

Results below

[sureshsonti]

Total Hours is only showing 7:43 hours. I got the first part which is hours worked by each employee. But not able to get the total hours worked by all employees. Thanks for your help

[MitchM]

I am not sure if this will be the type of solution you want, but here is a way to do it:

1. Create 2 Columns:

    (set data type to time) 

Time Worked = (Sheet1[Punch Out] - Sheet1[Punch In])

     (set data type to number) 

Minutes Worked = 
VAR TimeWorked = Sheet1[Punch Out] - Sheet1[Punch In]
VAR Hours = HOUR(TimeWorked)*60
VAR Minutes = MINUTE(TimeWorked)
RETURN
(Hours + Minutes)

2. Create 2 measures

 This will allow you to sum up hours for each employee. The total will not work, so turn it off for the chart to avoid confusion.

Total Time worked = CALCULATE(SUM(Sheet1[Time Worked]))

This will create a string that displays the sum of all hours worked. Use a card to display the value.

Sum of Time Worked = 
VAR HoursWorked = ROUNDDOWN(SUM(Sheet1[Minutes Worked])/60,0)
VAR MinutesWorked = SUM(Sheet1[Minutes Worked])-(HoursWorked*60)
RETURN
HoursWorked & ":" & MinutesWorked

Here is the final result

[kizzajamesmusis]

Hi,

I would like to know, in the first column, why are we multiplying with the 60?.

Then in the measure, why are we multiplying by 60 in the VAR for hours and then in the minutes VAR we decide by 60

THANKS

[Anonymous]

Test (Hour:Minute Diff) = FLOOR(24 * ('Table'[LastDate] - 'Table'[FirstDate]),1)
&":"&
FLOOR(24 * 60 * ('Table'[LastDate] - 'Table'[FirstDate]),1) - FLOOR(24 * ('Table'[LastDate] - 'Table'[FirstDate]),1) * 60

[Greg_Deckler · Accepted solution]

Check out this article:

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

[MitchM]

If you want to do the final sum as a PowerBI visual, but do the analysis in Power Query, you can add a column in Power Query that subtracts the Punch In Time from the Punch Out Time (Below). If you want to sum within Power Query you can then use the Transform--> Group By function Group by Employee ID and Have the new column Sum the Time Worked Column (also below)

[sureshsonti]

I am using a direct query report. Power Query not letting me create a custom column. I used this DAX formula and I get all 00:00 values. Data Type is Time

TODAY_HOURS = DATEDIFF('tick'[drvt.punch_in_time_1], 'tick'[drvt.punch_out_time_1],HOUR)