topic Re: Converting Decimal Hours to Time Format (Issue with negative values) in DAX Commands and Tips

Converting Decimal Hours to Time Format (Issue with negative values)

saadat_rda — Mon, 14 Nov 2022 07:16:08 GMT

Hi All,

I have a dataset with the time columns: Estimated Hours, Actual Hours and Remaining Hours.

I am pulling my data from an SQL server and the time fields are pulling through as decimal data types. So time of 45 minutes is actually showing as 0.75 hours.

I'm aware that there have been similar questions in this forum and the solution that I got from them was:

Actual Hours = VAR total_time = SELECTEDVALUE('Table'[ActualHours])
VAR hrs = INT(total_time)
VAR mins = (total_time - hrs) * 60
RETURN
FORMAT( hrs, "00") & ":" & FORMAT(mins,"00")

This has worked for the columns Estimated Hours and Actual Hours as they are never negative values.

However, Remaining hours is the differrence between the above two so sometimes the this column can have negative values and whenever there is, the formula gives the incorrect value.

Below is a screenshot and the highlighted row should have -10 in there instead of -1:50. Is anyone able to suggest an alternative please?

Re: Converting Decimal Hours to Time Format (Issue with negative values)

Anonymous — Tue, 15 Nov 2022 09:40:38 GMT

Hi saadat_rda ,

Here are the steps you can follow：

1. Create calculated column.

Flag = var _leftEST=VALUE(LEFT('Table'[Estimate Hours],2)) var _leftACT=VALUE(LEFT('Table'[Actual_Hours],2)) var _midEST=VALUE(MID('Table'[Estimate Hours],4,2)) var _midACT=VALUE(MID('Table'[Actual_Hours],4,2)) var _left= _leftEST - _leftACT var _mid= _midEST - _midACT return SWITCH( TRUE(), _left=0 && _mid<0, "-"&""&(_left+1)&":"&60 +_mid, _left >=0 && _mid>=0,_left &":"&_mid, _left >=0 && _mid<0,_left +1 &":"& 60 +_mid, _left <0 && _mid>=0, _left&":"& _mid, BLANK() )

Remaining Hours = var _len= LEN('Table'[Flag]) var _left=LEFT('Table'[Flag],1) return SWITCH( TRUE(), _len=5&&_left="-","-0"&""&MID([Flag],2,4), _len=3,"0"&""&[Flag]&""&"0", _len=4,"0"&""&[Flag], [Flag] )

2. Result:

Best Regards,

Liu Yang

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

Re: Converting Decimal Hours to Time Format (Issue with negative values)

saadat_rda — Tue, 15 Nov 2022 21:56:06 GMT

Thanks Liu. It worked and I really appreciate this!