Forum Discussion
Calculate Working Hours per Week
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
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.
2 Replies
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)
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.
