cancel
Showing results 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

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
Super User

@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
New Member

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)))

5 REPLIES 5
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))
)``````

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

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)))

Any idea why the positive time is not converting?

New Member

Thank you so much.  I will try this!

Super User

@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
New Member

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)))