Calculating Working hours

[Roma28]

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 (
        ADDCOLUMNS (
            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 )

[v-frfei-msft]

Hi @Roma28 ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

[Roma28]

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

https://luissmy.sharepoint.com/:u:/g/personal/luca_romano_studenti_luiss_it/EeACcVnEIxNHiuCL6fmcpc8B... --> powerBi file

https://luissmy.sharepoint.com/:x:/g/personal/luca_romano_studenti_luiss_it/EdF8jYf2S8hIltb8ROG0vVkB... --> excel table

[v-frfei-msft]

Hi @Roma28 ,

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

[Roma28]

@v-frfei-msft 

https://drive.google.com/drive/folders/1Ks7rL5ROAV73SD1mRd-NbWrwfXbdI9vP?usp=sharing

Try now ! I created a folder on Google Drive

Tell me if you have more trouble to access it