Calculate hours

[James_Galis1]

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 the start time and end time for each day and calculate the available hours for each employee 

EG Employee 1, for 28/6/23 starts at 9:30 and end time is 15:00, "Available hours" = 5.5

The column " available hours " has been entered manually to show the expected result. 

Client	Visit Date	Day	Start Time	End Time	Employee  Name	Hours		Available Hours
Client 1	27/06/2023	Tuesday	12:00	13:00	Employee 1	1		1
Client 4	28/06/2023	Wednesday	9:30	10:00	Employee 1	0.5		5.5
Client 5	28/06/2023	Wednesday	10:00	11:00	Employee 1	1		5.5
Client 2	28/06/2023	Wednesday	11:00	12:00	Employee 1	1		5.5
Client 3	28/06/2023	Wednesday	14:00	15:00	Employee 1	1		5.5
Client 7	29/06/2023	Thursday	10:00	11:00	Employee 1	1		6
Client 8	29/06/2023	Thursday	13:00	14:00	Employee 1	1		6
Client 6	29/06/2023	Thursday	15:00	16:00	Employee 1	1		6
Client 9	30/06/2023	Friday	11:00	12:00	Employee 1	1		4.5
Client 10	30/06/2023	Friday	15:15	15:30	Employee 1	0.25		4.5
Client 11	26/06/2023	Monday	9:30	10:30	Employee 2	1		2.5
Client 13	26/06/2023	Monday	11:00	13:00	Employee 2	2		2.5
Client 14	27/06/2023	Tuesday	9:30	10:30	Employee 2	1		3.5
Client 12	27/06/2023	Tuesday	11:00	13:00	Employee 2	2		3.5
Client 12	28/06/2023	Wednesday	9:00	11:00	Employee 2	2		4.15
Client 11	28/06/2023	Wednesday	11:15	13:15	Employee 2	2		4.15
Client 15	29/06/2023	Thursday	11:30	13:30	Employee 2	2		2
Client 16	30/06/2023	Friday	9:30	11:30	Employee 2	2		4.5
Cleint 11	30/06/2023	Friday	12:00	14:00	Employee 2	2		4.5

Much appreciated

[Ritaf1983 · Accepted solution]

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]

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

[James_Galis1]

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 4	28/06/2023	Wednesday	9:30	10:00	Employee 1	0.5		5.5
Client 5	28/06/2023	Wednesday	10:00	11:00	Employee 1	1		5.5
Client 2	28/06/2023	Wednesday	11:00	12:00	Employee 1	1		5.5
Client 3	28/06/2023	Wednesday	14:00	15:00	Employee 1	1		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 · Accepted solution]

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]

Thank You

This was what i was looking for

[Ritaf1983]

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

[James_Galis1]

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 4	28/06/2023	Wednesday	9:30	10:00	Employee 1	0.5		5.5
Client 5	28/06/2023	Wednesday	10:00	11:00	Employee 1	1		5.5
Client 2	28/06/2023	Wednesday	11:00	12:00	Employee 1	1		5.5
Client 3	28/06/2023	Wednesday	14:00	15:00	Employee 1	1		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

[Ritaf1983 · Accepted solution]

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

[James_Galis1]

Thank you so much

This helps me out immensely 

[Ritaf1983]

Happy to help 🙂