Calculate total hours worked

[RussHaight]

This is from SQL backend.I have table named Punch. It has 1 field named TotalTimeHHMM in type text. I want to sum/add the TotalTimeHHMM. The values are in this format. 10:19 which reperesents 10 hours 19 minutes. This field also has leading 0's. an example would be 02:57 which represents 2 hours 57 minutes. I do not want to breakout in days, just hours and minutes. An example would be this-18:30 + 15.45 + 12.11 which would equal adding the hours of 18+15+12 for 45 and adding the minutes of 30+45+11 which would equal 86 minutes or 1 hour 26 minutes for a grand total of 46 hours and 26 minutes -45 hours + 1 hour 26 minutes.

Thank you in advance.

[RussHaight · Accepted solution]

I figured it out.

Instead of using the TotalTime column which was a conversion fromTotalMinutes column, I just used TotalMinutes column. Example as follows

TotalMinutes 598.8 (decimal) converts to TotalTime 09:59 (text) with 09 being hours and 59 being minutes. I created a new column with the following dax formula 

Total Time =

    VAR TotalMinutes = 'punch'[Total Minutes]

    RETURN

        FORMAT(QUOTIENT(TotalMinutes, 60), "0") & " hours " & FORMAT(MOD(TotalMinutes, 60), "0") & " minutes"

which gave me an output of 9 hours 59 minutes. 

I created a measure using the same dax formula and was able to drop it on a card to get total hours worked in hours/minutes output. I then added 2 slicers, one for location and one for employee.

There might be a better way of doing this but this is what I did to get it to work.

[Anonymous]

Hi @RussHaight ,

Thank you for providing the solution.

If the problem has been solved, please mark it as solution. if there is still a problem, please point it out and I will answer it for you as soon as possible.

Best Regards,
Adamk Kong