The other day, I saw a good question over on Reddit about “Converting Decimal to Minutes with Seconds”. So if you had a numeric value of 353.10, convert that number over to a hh:mm:ss, like 05:53:06. I ended up making a YouTube video about this, but we’ll step through this here in this post as well, step-by-step.
If by chance, you don’t want to learn and thoroughly understand how we convert these values, and you just want to get at the formula you need, look no further, because here it is (just replace “YOUR_TIME_VARIABLE” with your variable or numeric control value):
With(
{DecimalValue: YOUR_TIME_VARIABLE},
Text( Trunc(DecimalValue / 60), "00" ) & ":" &
Text( Trunc(Mod(DecimalValue, 60)), "00" ) & ":" &
Text( Trunc(Value(
With(
{SecondsDecimal:
If(IsBlank(Find(".",
Text(DecimalValue))),
0,
Left(Last(Split(Text(DecimalValue), ".")).Result, 2) )
},
If(Len(SecondsDecimal) = 1,
SecondsDecimal & "0", SecondsDecimal
)
)
) * 60 / 100),
"00")
)
The first step to break things down and understand how we got at the end result formula, is to separate values and steps down with controls.
- Add a slider control to increase/decrease the decimal numeric value with represents how many minutes have passed in a time span.
- Set the Max to 86,400, which is how many seconds there are in a day. If we do this, we’ll know that if we slide to the middle, the end result should be somewhere around 12 hours (more or less a little), and if we slide it to all the way to the right, we’ll end up with 24 hours.
- Next, change the OnChange to update a variable. This could be a global variable or a context variable. Because we are only using this variable on this screen, why not use UpdateContext function to make it a context variable.
- Add three labels below the slider for hours, minutes, seconds:
- One for Hours – Text: Trunc(DecimalTime / 60)
- Trunc will remove the decimal portion we don’t need
- One for Minutes – Text: Trunc(Mod(DecimalTime, 60))
- Trunc will remove the decimal portion we don’t need
- One for Seconds – Text: Value(Last(Split(Text(DecimalTime), “.”)).Result) / 10
- this one will need to be broken down much more, since it is more complicated (See bulleted list below).
- One for Hours – Text: Trunc(DecimalTime / 60)
- Add in a label at the bottom of all these which will pull it all together which will hold the end result.
You’ll notice that the seconds portion isn’t quite right. There are a few more steps we need to knock out before we get to the value we need:
- We need to know if the value is zero. If the numberic value is reduced to a zero with no decimal point, there’s no need to try to do the next steps.
- If there is a decimal value, we need to grab only the first two numbers after the decimal. We’re just interested in whole seconds, not fractions of seconds in this use case.
- If we only get one number after the decimal, then we need to add a zero to the end. For example, 0.1 minutes is a tenth (10) and not a hundredth (1), which would give us very different results.
- In order to convert a decimal variable/100 to a time, unknown/60, we know from High School Algebra class, we should multiply these fractions and solve for x (the unknown value). We do this by multiplying our left over decimal portion of our number, multiplying it by 60, then dividing by 100.
- NOTE: I go over all this in much more depth in the video at mark 5:30, which you might find very interesting!
At last, this give us our needed value for seconds! Be sure to grab the fully finished expression at the top of this post.
And here is the end result we achieved in the video. We just accomplished it with extra steps over 38 minutes:
Download the file here: https://github.com/PowerAppsDarren/TimeConvertFromDecimal
