Forum Discussion

saadat_rda's avatar
saadat_rda
Frequent Visitor
3 years ago
Solved

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

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?

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      saadat_rda
      Frequent Visitor
      3 years ago

      Thanks Liu. It worked and I really appreciate this!