cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating Working hours

Hi,

i want to calculate the working hours that passed between the arrive of an email and the final answer to the client.

I found a very good script at this link https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374828#M170248 but there is a problem into it that i can't solve.

The formula starts to count the hours from the time in which the email arrived but it applies the same time for starting the count also on subsequent days; the fact is for the days in the middle i want the formula to take into consideration all the working hours (8:30 - 19:30). For example, if an email arrived at 12:25 of 29/03/19 the formula will count from that time (and it's fine), but it will do the same also for 30/03/19 (i want to change this).

Basically i want to add another line that manages the count for the days in the middle.

I was thinking to something like:

VAR hourcount =

COUNTROWS (

FILTER (

filtered,

( [Date] > DATEVALUE ( [ACTIVITY_DATE] )

&& [Hour] > HOUR ( [ACTIVITY_DATE] ) + 1 )

&& ( [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )

&& [Hour] > HOUR ( [LASTMODIFIEDDATE] ) - 1 ) ) )

&&( [Date] > DATEVALUE ( [ACTIVITY_DATE] +1 )

&& [Hour] > HOUR ( 9:00 ) )

Could someone help me?Starting Date (left) & End Date (right)

The formula:

```Work Hour =
VAR filtered =
FILTER (
CROSSJOIN (
CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
),
"Day of week", WEEKDAY ( [Date], 2 )
),
[Day of week] < 6
&& [TicketID] = EARLIER ( Table1[TicketID] )
)
VAR hourcount =
COUNTROWS (
FILTER (
filtered,
(
[Date] >= DATEVALUE ( [ACTIVITY_DATE] )
&& [Hour]
> HOUR ( [ACTIVITY_DATE] ) + 1
)
&& (
[Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
&& [Hour]
> HOUR ( [LASTMODIFIEDDATE] ) - 1
)
)
)
VAR remained =
DATEDIFF (
TIMEVALUE ( [ACTIVITY_DATE] ),
TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
MINUTE
)
+ DATEDIFF (
TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
TIMEVALUE ( [LASTMODIFIEDDATE] ),
MINUTE
)
RETURN
IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )```

4 REPLIES 4
Community Support Team

## Re: Calculating Working hours

Hi @Roma28 ,

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculating Working hours

Hi Frank @v-frfei-msft

thanks for the help !! I have sensible data in the original file, so i created a new one with just index, start & end date

Community Support Team

## Re: Calculating Working hours

Hi @Roma28 ,

I cannot access the link as you shared. Could you please share the files again by another way?

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor