Forum Discussion

RussHaight's avatar
RussHaight
New Member
2 years ago
Solved

Calculate Time

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

Need Help
  • RussHaight's avatar
    RussHaight
    2 years ago

    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

7 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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.
    • RussHaight's avatar
      RussHaight
      New Member
      2 years ago

      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's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        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'
    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      Hi, RussHaight 

       

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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 NameEnrolment Start TimeEnrolment End Time
    Complaints Handling 15/01/20248:00 AM5:00PM
    Motor Lodgement 05 - 23/02/20248:30 AM4:30PM