Forum Discussion

James_Galis1's avatar
James_Galis1
Helper II
3 years ago
Solved

Calculate hours

Hello All   Would appreciate some assistance with the following.   I have 1 table with employee schedules that shows the time and date of visits I wish to have a calculated column that looks at ...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi James_Galis1 again:)
    If i understans you correctly after yoour response then something like that:
    (I have done it in 3 steps to make it easier to follow , you can combine to 1)
    1. Calculate for every employee first start time for each day :

    First_Start_time_Employee = CALCULATE(MIN('Table'[Start Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
    )

    2. Do the same with last end time:

    Last_End Time_time_Employee = CALCULATE(MAX('Table'[End Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
    )
    3. Calculate difference between them in minuts and divide by 60 :
    Hours_Availble = DATEDIFF('Table'[First_Start_time_Employee],'Table'[Last_End Time_time_Employee],MINUTE)/60

    Link to the sample file 

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

  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi James_Galis1 
    You can summarize your total worked hours with the following:

    Hours worked = CALCULATE(sumx('Table',DATEDIFF('Table'[Start Time],'Table'[End Time],MINUTE))/60, ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
    )

     

    and then calculate the delta:

    Hours_Diff = [Hours_Availble]-[Hours worked]

    I updated a sample file with those new columns.

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

     

Ritaf1983's avatar
Ritaf1983
Super User
3 years ago

Hi James_Galis1 
My apologies, I didn't fully understand the calculation.
Are you interested in aggregation by day and employee?

  • James_Galis1's avatar
    James_Galis1
    Helper II
    3 years ago

    Hi

    Thank you for looking into this. I think the "hoursdiff" looks more like the " hours" column 


    I need to ensure the unworked hours for each date is captured accordingly

     

    Eg

    Client 428/06/2023Wednesday9:3010:00Employee 10.5 5.5
    Client 528/06/2023Wednesday10:0011:00Employee 11 5.5
    Client 228/06/2023Wednesday11:0012:00Employee 11 5.5
    Client 328/06/2023Wednesday14:0015:00Employee 11 5.5

     

    The total hours worked =3.5

    The available hours (9:30 - 15:00) = 5.5

     

    The gap of 2 hours which is between client 2 and client 3 is what i need bring into calculation when considering the available hours column

     

    Thanks

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      3 years ago

      Hi James_Galis1 again:)
      If i understans you correctly after yoour response then something like that:
      (I have done it in 3 steps to make it easier to follow , you can combine to 1)
      1. Calculate for every employee first start time for each day :

      First_Start_time_Employee = CALCULATE(MIN('Table'[Start Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
      )

      2. Do the same with last end time:

      Last_End Time_time_Employee = CALCULATE(MAX('Table'[End Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
      )
      3. Calculate difference between them in minuts and divide by 60 :
      Hours_Availble = DATEDIFF('Table'[First_Start_time_Employee],'Table'[Last_End Time_time_Employee],MINUTE)/60

      Link to the sample file 

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

      • James_Galis1's avatar
        James_Galis1
        Helper II
        3 years ago

        Thank You

         

        This was what i was looking for