Forum Discussion
Helper IConverting Minutes to DDHHMM format, negative not showing correctly
Hello,
I have not been able to find an answer to these questions.
I have duration in minutes from the system. PBI Datediff gives the same results. No issue.
_ Datediff PartOrder to Delivered Minute =
DATEDIFF([Part Order],Part Delivered],MINUTE)
I am using this to display my Duration-In-Minutes as a 00D 00H 00M format.
It works for positive minutes, but for negative it is showing incorrect.
_PartOrder to PartDelivered DHM =
var Days=INT([PartOrder to PartDelivered Minutes]/1440)
var Hours=INT(MOD([PartOrder to PartDelivered Minutes] / 1440,1 ) * 24)
var Minutes= INT(MOD(MOD([PartOrder to PartDelivered Minutes]/1440,1)*24,1)*60)
RETURN
FORMAT(Days,"#00") & "D " & FORMAT(Hours,"#00") & "H " & FORMAT(Minutes,"#00") & "M"
So I went and added a DateDiff for each, Days Hours Minutes. And added this column as Formatted. It does show correctly.
Format =
FORMAT([Order to Deliver Days],"#00") & "D " & FORMAT([Order to Deliver Hours],"#00") & "H " & FORMAT([Order to Deliver Minutes],"#00") & "M"
Questions:
1) Is there a way to consolidate the measures? Or is it correct?
2) Since Formatted is, well, formatted, you can't use it to sort - as you could with the Minutes. Is there a way to fix this?
Any help is appreciated. Thank you!!
Hi min-E ,
The error in your original code is due to the presence of a negative number, which can cause an error when performing math calculations. You can add a judgment condition to your original code. For example:_PartOrder to PartDelivered DHM = VAR Days = IF( [PartOrder to PartDelivered Minutes] >= 0, TRUNC([PartOrder to PartDelivered Minutes] / 1440), TRUNC((-[PartOrder to PartDelivered Minutes]) / 1440) ) VAR Hours = IF( [PartOrder to PartDelivered Minutes] >= 0, TRUNC(MOD([PartOrder to PartDelivered Minutes], 1440) / 60), TRUNC(MOD((-[PartOrder to PartDelivered Minutes]), 1440) / 60) ) VAR Minutes = IF( [PartOrder to PartDelivered Minutes] >= 0, MOD([PartOrder to PartDelivered Minutes], 60), MOD((-[PartOrder to PartDelivered Minutes]), 60) ) RETURN IF( [PartOrder to PartDelivered Minutes] >= 0, CONCATENATE( Days & "D " & Hours & "H " & Minutes & "M", "" ), CONCATENATE( (0-Days) & "D " & (0-Hours) & "H " & (0-Minutes) & "M", "" ) )If it is a negative number, it is first converted to a positive number for calculation.
And the final output is as below:
Regarding the sorting question, since your column is in your custom format, it is a text type column, and text types can't sort data from largest to smallest like the [MINUTE] column. Although you can do this by customizing the sorting, it requires you to define a sort order for each row in the table, which is quite cumbersome and I don't recommend it.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi min-E ,
The error in your original code is due to the presence of a negative number, which can cause an error when performing math calculations. You can add a judgment condition to your original code. For example:_PartOrder to PartDelivered DHM = VAR Days = IF( [PartOrder to PartDelivered Minutes] >= 0, TRUNC([PartOrder to PartDelivered Minutes] / 1440), TRUNC((-[PartOrder to PartDelivered Minutes]) / 1440) ) VAR Hours = IF( [PartOrder to PartDelivered Minutes] >= 0, TRUNC(MOD([PartOrder to PartDelivered Minutes], 1440) / 60), TRUNC(MOD((-[PartOrder to PartDelivered Minutes]), 1440) / 60) ) VAR Minutes = IF( [PartOrder to PartDelivered Minutes] >= 0, MOD([PartOrder to PartDelivered Minutes], 60), MOD((-[PartOrder to PartDelivered Minutes]), 60) ) RETURN IF( [PartOrder to PartDelivered Minutes] >= 0, CONCATENATE( Days & "D " & Hours & "H " & Minutes & "M", "" ), CONCATENATE( (0-Days) & "D " & (0-Hours) & "H " & (0-Minutes) & "M", "" ) )If it is a negative number, it is first converted to a positive number for calculation.
And the final output is as below:
Regarding the sorting question, since your column is in your custom format, it is a text type column, and text types can't sort data from largest to smallest like the [MINUTE] column. Although you can do this by customizing the sorting, it requires you to define a sort order for each row in the table, which is quite cumbersome and I don't recommend it.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
min-EDino, thank you for taking the time to explain. This absolutely makes sense.
As far as sorting, thanks for the confirmation. The minutes can be used if it's that big of a deal.