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

[saadat_rda]

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?

[v-yangliu-msft · Accepted solution]

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

[saadat_rda]

Thanks Liu. It worked and I really appreciate this!