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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Daryl33609
New Member

Negative and positive time to decimal

I have a column of time duration that is formatted as text.  If I try and format the column any other way I lose my negatives.  I need to convert negative and positive time durings such as  -48:00 and -07:47 to time decimal representation such as -48.00 and -7.78.  Does anyone have any suggestions?  Thank you in advance.

2 ACCEPTED SOLUTIONS

@Daryl33609 
If it works accept the asnwer as solution otherwise explain the issue/requirment  

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Ignore my prior post.  I made a few more tweaks to your code and got it to work.  Thank you so much for getting me past this issue.  Here is my code:

Column 2 = SWITCH (

        TRUE (),

        LEFT ( [Actual Total Hours (Include Corrections)],1) = "-", (MID([Actual Total Hours (Include Corrections)],2,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)) * - 1,

        LEFT ( [Actual Total Hours (Include Corrections)],1) <> "-", (MID([Actual Total Hours (Include Corrections)],1,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)))

 

View solution in original post

5 REPLIES 5
Fowmy
Super User
Super User

@Daryl33609 

You can add a column as follows. I assume you have + and - for each value

Col = 
SWITCH (
        TRUE (),
        LEFT ( [Value],1) = "-", (MID([Value],2,2) + (right ( [Value] , 2 ) /60)) * - 1,
        LEFT ( [Value],1) = "+", (MID([Value],2,2) + (right ( [Value] , 2 ) /60)) 
    )

Fowmy_0-1653250507804.png

 





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I am very close, thanks to your script.  The negative transformation is working, but not the positive.  Here is my code:

Column 2 = SWITCH (
TRUE (),
LEFT ( [Actual Total Hours (Include Corrections)],1) = "-", (MID([Actual Total Hours (Include Corrections)],2,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)) * - 1,
LEFT ( [Actual Total Hours (Include Corrections)],1) = "+", (MID([Actual Total Hours (Include Corrections)],2,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)))
Daryl33609_0-1653388821814.png

Any idea why the positive time is not converting?

Thank you for your assistance.

Thank you so much.  I will try this!

@Daryl33609 
If it works accept the asnwer as solution otherwise explain the issue/requirment  

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ignore my prior post.  I made a few more tweaks to your code and got it to work.  Thank you so much for getting me past this issue.  Here is my code:

Column 2 = SWITCH (

        TRUE (),

        LEFT ( [Actual Total Hours (Include Corrections)],1) = "-", (MID([Actual Total Hours (Include Corrections)],2,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)) * - 1,

        LEFT ( [Actual Total Hours (Include Corrections)],1) <> "-", (MID([Actual Total Hours (Include Corrections)],1,2) + (right ( [Actual Total Hours (Include Corrections)] , 2 ) /60)))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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