Calculate Working Hours per Week

Hi HenryJS ,

Try to create a column like so:

Column 2 = 
VAR DateDiffSeconds =
    DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
    TRUNC ( Hours2 )
VAR Minutes =
    TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
RETURN
    Hours & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )

Then, you can convert the column type from "Text" to "Time". Or just change the expression like below and then choose datatype as "Time".

Column 3 =
VAR DateDiffSeconds =
    DATEDIFF ( [StartTime], [EndTime], SECOND )
VAR Hours1 = DateDiffSeconds / 3600
VAR Hours2 = Hours1 * 5 - 2.5
VAR Hours =
    TRUNC ( Hours2 )
VAR Minutes =
    TRUNC ( ( Hours2 * 3600 - Hours * 3600 ) / 60 )
VAR Seconds = Hours2 * 3600 - Minutes * 60 - Hours * 3600
VAR Result =
    Hours & ":"
        & FORMAT ( Minutes, "00" ) & ":"
        & FORMAT ( Seconds, "00" )
RETURN
    CONVERT ( Result, DATETIME )

BTW, .pbix file attached.

Best Regards,

Icey

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