Calculate Time

[RussHaight]

Hello,

I'm trying to figure out a way to calculate time diff. In the table the time in and time out are in the same field with a punch type determining if it was in or out

Table structure is like this.

punch date/time                        employee #    punch time     punch type

9/11/2023 8:00:00 am                 00000            8:00 am               ID

9/11/2023 4:00:00 pm                 00000            4:00 pm              OD

ID being in day and OD being out day

Thank you in advance

[Keisha333]

I  am trying to caluculate the time for each course. I have a start time and end time for the trianing and waniting to add another column that shows the total hours of each course 

Enrolment Name	Enrolment Start Time	Enrolment End Time
Complaints Handling 15/01/2024	8:00 AM	5:00PM
Motor Lodgement 05 - 23/02/2024	8:30 AM	4:30PM

[Dangar332]

hi, @RussHaight 

try below measure. It might help

timediff =

var g =MIN('time'[punch date/time])

var a = CALCULATE(MAX('time'[punch date/time]),'time'[punch type]="id")

var b = CALCULATE(MAX('time'[punch date/time]),'time'[punch type]="od")

var c =CALCULATE(DATEDIFF(a,b,HOUR),'time'[punch date/time]=g)

return

c

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Dangar332]

Hi, @RussHaight 

If above post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[RussHaight]

Thank you for your reply. Copying and pasting what you wrote throws a lot of red so I changed it to what i think you are explaining to me but I get the following error. 

Also, is this formula supposed to be a measure or an added column on the punch table? 

[Dangar332]

hi, @RussHaight 

it's a measure.

and remove   ))  from code  it give you answer

timediff =

var g =MIN('time'[punch date/time])

var a = CALCULATE(MAX('time'[punch date/time]),'time'[punch type]="id")

var b = CALCULATE(MAX('time'[punch date/time]),'time'[punch type]="od")

var c =CALCULATE(DATEDIFF(a,b,HOUR),'time'[punch date/time]=g)

return

c

in above code red part is table name i use as 'time' so replace your table name with 'time'

[RussHaight]

the red ) is added automatically when I click out of the code window and if I hit enter again while in the code window, it adds another one, and so on.

so after Return

c

I click out and it adds one ), I click in and then out, it adds another one ))

[RussHaight · Accepted solution]

sorry-had an extra ], I removed it now I don't get the red )

Thank you-I'm going to see if I can leverage a measure as to what I am trying to do-which is calculate hours workd by employee_number on any given day/week/month/etc