Forum Discussion

James_Galis1's avatar
James_Galis1
Icon for Helper II rankHelper 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
James_Galis1's avatar
James_Galis1
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper II rankHelper II
    3 years ago

    Thank You

     

    This was what i was looking for

    • Ritaf1983's avatar
      Ritaf1983
      Icon for Super User rankSuper User
      3 years ago

      Glad to help, I apologize for the misunderstanding at first 🙂

      • James_Galis1's avatar
        James_Galis1
        Icon for Helper II rankHelper II
        3 years ago

        Hi Rita

         

        The solution works perfectly.

        There is another requirement that has just come up that im currenty working through  but having little trouble with

         

        Following on from the solution you have provided.

         

        Is it possible to have a measure or column that subtracts the total hours from the available hours ?

         

        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

         

        For the day of the 28/6

         

        Employee 1 has total hours  worked ( 3rd last column )= 3.5 and available hours( last column ) =5.5.

         

        The result  im looking to acheive for the 28/6 would 2 ( 5.5 - 3.5 )

         

        There is no issue is summing up the hours worked but not sure how to go about just looking at the highest value on the given date and come up with the calculation

         

        Cheers