Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
saadat_rda
Frequent Visitor

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?

 

Decimal to Time.PNG

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1668505148110.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1668505148110.png

 

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

Thanks Liu. It worked and I really appreciate this!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors