Rounding up time

[Anonymous]

Hey guys, I've got the following situation below and basically in the column where the time is, I need to round it up to only hours, if its above 40 minutes. So lets say its 15:30 then it displays 15, but if its 15:40 then it rounds up to 16 hours. Any way to achieve that by adjusting the code?
Any help appreciated. 

[Fowmy]

@Anonymous 

Time rounded as expected:

Rounded Time = 
IF( MINUTE([TIME]) >= 40,  
    MROUND([Time], 1/24 ),
    HOUR([Time])/24
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy @mahoneypat This is what i get: Might be the reason that Im using charlie eidens duration and displaying time as a whole number.

[Fowmy]

@Anonymous 

My formula is based on Time data type column, if you have any other type, I need to do it differently.

You may share a sample PBIX file to check.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[Anonymous]

@Fowmy @mahoneypat  You will find my pbix file here: https://gofile.io/d/Vsei2l The column name is NewTIme

[Fowmy]

@Anonymous 

I modified the formula, you can try now:

Rounded Time = 

VAR T = TIME(0,0,[Time]) RETURN
IF( MINUTE(T) >= 40,  
    MROUND(T, 1/24 ),
    HOUR(T)/24
)

If you want to extract the hour as a number then apply the below formula and format it as the Whole Number

Rounded Time = 

VAR T = TIME(0,0,[Time]) RETURN
HOUR(
IF( MINUTE(T) >= 40,  
    MROUND(T, 1/24 ),
    HOUR(T)/24
)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy  This is the error message that I get: 

[Anonymous]

Or if i extract just the hour, I get zeros: 

[Fowmy · Accepted solution]

@Anonymous 

You need to use the seconds as input for my formula, it converts it into time. 

I have attached your file with the column added.

You can download the file: HERE

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy Ive got it working now, however one of the drive time was 24:52 and when it rounds up it produces 1.

[Fowmy]

@Anonymous 

That is correct,  24 is midnight, 24:57 is next day 1am when rounded up. 

hope this clarifies 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy  Yes that does make sense, however I would need to display 25, as in my vizualisations im displaying hours, any way to adjust the code?

[v-lionel-msft]

Hi @Anonymous ,

Try this formula.

Column = 
VAR x = FORMAT( [Trukmė Prastova Radviliškis Convert], "00:00")
VAR __right = RIGHT(x, 2)
VAR __left = LEFT(x, 2)
RETURN
IF(
    __right >= "40",
    __left+ 1,
    __left-0
)

Best regards,
Lionel Chen

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

[Anonymous]

@Fowmy  This does make sense, however, I need the column to be in the whole number format rather than time. 

[Anonymous]

This is the code I use for my time. 

Trukmė Prastova Radviliškis Convert = VAR Duration = SUM ( 'Avg By Day'[avg_time] ) // There are 3,600 seconds in an hour VAR Hours = INT ( 'Avg By Day'[avg_time] / 3600 ) // There are 60 seconds in a minute VAR Minutes = INT ( MOD ( 'Avg By Day'[avg_time] - ( Hours * 3600 ), 3600 ) / 60 ) VAR Seconds = ROUNDUP ( MOD ( MOD ( 'Avg By Day'[avg_time] - ( Hours * 3600 ), 3600 ), 60 ), 0 ) RETURN Hours * 100 + Minutes

[mahoneypat]

Please try a column expression like this to get your desired result.

Rounded Time = var hours = HOUR('Table'[Time])

var minutes = MINUTE('Table'[Time])

return IF(minutes>=40, hours +1, hours)

Regards,

Pat