Calculate Working Hours per Week

[HenryJS]

Hi all,

How can I calculate hours per week?

I have calculated hours per day

Hours per day = [starttime] -  [endtime]

data type is 'Time'

I want to multiply this figure by 5 and minus 2.5h

if i do

Hours per week = ([Hours per day] * 5)-2.5

but it doesn't work

[Icey · Accepted solution]

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.

[amitchandak]

@HenryJS ,Try like

Hours per Week= datediff( [starttime] ,[endtime],Hour)*5 -2.5

If you want to convert back to time

use

Total time = time([Hours per Week]),0,0)