Anyone know how I can convert decimal time (10.4 Hours) to Hours and Minutes format? I have a formula field that subtracts the start time from the finish time and shows a number like: "10.4" I need this number to look more like: 10:24:00.
Thanks in advance
This literally totally and completely solved a problem for me. Thank you!
You'll need a combination of Equation Fields and a Text formula field with regular expressions to make this work. Let's use 10.24 hrs as an example.
1) Create fields for DECIMAL TIME(Number), HOURS(Equation), MINUTES(Equation), and SECONDS(Equation)
This sets up our variables for making the time readout
2) Set all fields except for DECIMAL TIME to 'Round Down' from the Rounding pulldown menu and '0' in Decimal Places
This will give us the absolute values for Hour, Minute, and Second variables
3) For all fields except DECIMAL TIME and HOURS set 'Format' to Custom with the value 0 in 'Before'
This is to generate two digits for every time value
5) Equation for HOURS should simply read {DECIMAL TIME}
For a Decimal Time value of 10.24 this will read 10
6) Equation for MINUTES is ({DECIMAL TIME}-{HOURS})*60
For Decimal Time value 10.24 the equation will be (10.24-10)*60=14.4 which will read as 14
7) Equation for SECONDS is ((({DECIMAL TIME}-{HOURS})*60)-{MINUTES})*60
((10.24-10)*60)-14)*60=24
So we are left with
DECIMAL TIME = 10.24
HOURS = 10
MINUTES = 14
SECONDS = 24
To string that together, you will need to create a text equation. This Text Equation will read:
{HOURS}:{MINUTES}:{SECONDS}
BUT WAIT!
In order to make sure you don't end up with all of those extra zeroes in your equation, you'll need to use a Regular expression
right(string, 2)
This takes only the last two alphanumerics in a given string of characters
So . . . the final equation reads:
right({HOURS}, 2):right({MINUTES}, 2):right({SECONDS}, 2)
**Be sure to include a space before '2')