You need to sign in to do that

Don't have an account?

leah brooks 11

# Convert Text Field to dd:hh:mm Number/Time Field

Hello,

I need to make a formula which will take a text field formatted like so: "0days 0hours 0minutes" and turn it into a number which could be output it in a stopwatch fashion: 00:00:00 so that I can use it to pull accurate reports.

I tried using the VALUE() fuction, but that did not seem to work.

Here is the formula that is generating the text field, if that helps at all:

Thank you.

I need to make a formula which will take a text field formatted like so: "0days 0hours 0minutes" and turn it into a number which could be output it in a stopwatch fashion: 00:00:00 so that I can use it to pull accurate reports.

I tried using the VALUE() fuction, but that did not seem to work.

Here is the formula that is generating the text field, if that helps at all:

IF(ISBLANK( Complete_Date_Time__c ) , "", TEXT( FLOOR(Case_Open_Length__c ) ) & " days " & TEXT( FLOOR( MOD( (Case_Open_Length__c) * 24, 24 ) ) ) & " hours " & TEXT( ROUND( MOD( (Case_Open_Length__c) * 24 * 60, 60 ), 0 ) ) & " minutes" )

Thank you.

Nayana K## All Answers

Nayana K
Replace SomeFormula__c with text formula field which you mentioned and check if this works.

leah brooks 11That would work to change the format, but the system is still recognizing it as text. I need to get the system to somehow recognize it as a time, or a number so that I can use it in my reports.

Thank you for your quick response!!

leah brooks 11UPDATE: I added value to this formula and I believe it may work, however I am getting the 'compiled size is too big to execute' error now.

Nayana K00:00:00 This cannot be a number since it contains colon. So VALUE wont work even if there is no compile error. Also there is no time datatype in salesforce.

leah brooks 11What if a were to switch it into a decimal format? i.e h.m so if a case has been open for 1 day and 34 minutes it would show 24.34. Would that be possible? Nayana KYes, possible. Formula datatype Number with length 2 and decimal places 2.

IF(Time_Open__c != "", VALUE(TRIM(RIGHT(LEFT(Time_Open__c,FIND("hours", Time_Open__c)-1),2)) + "." + TRIM(RIGHT(LEFT(Time_Open__c,FIND("minutes", Time_Open__c)-1),2))), NULL)

leah brooks 11I tried to use that formula but it is telling me that it exceeds the compiled formula size, any idea on how to make it smaller? Would it be possible to generate the formula from the original formula that I am using that I pasted in my first post above? That would probably make it much smaller since it would take out a step. Nayana K
Are you looking for this? Replace previous number formula with this. I dont think this will exceed compile size because this is shorter than the original text formula which you have shared.

leah brooks 11leah brooks 11

Yes that worked! Thank you!

One more question. On my test case it said that the case had been open for 4 minutes in the text box, however in the numerical box it said 0.40, which in my mind would be 40 minutes, is there a way I can switch that around so it would put single digits in the tenths spot, and then once the number got to double digits it would populate like a normal decimal? i.e. (4 minutes= 0.04, 15mn = 0.15)

Also it does not seem to be factoring in the number of days that a case was open, only the hours, (i.e. 9 days 10 hours 40 minutes displays the same as 10 hours 40 mintes.)

Thank you so much! :)

Nayana KCreate a proxy text formula field: say proxyMinutes__c

and replace the intended number formula field with :

Number cannot hold 2 decimal points :

12.30.55 is not valid. So you cannot add dd.hh.mm here

leah brooks 11I used the proxy field, but when a case is open for les then ten minutes it is still being display as .60 instead of .06 Nayana KOh, for less than 10 minutes what value proxy field is returning? leah brooks 11the proxy field is returning a single digit value, for 8 minutes, proxy field is showing "8" I changed something with my original formula and it actually seems to be working now! One last question: Is there a way that I can multiple the 'days' field by 24 so that my my number field will be 100% accurate? so if a case has been open for 2 days, 3 hours, 4 minutes it would display as 51.04 ?

Thank you for all of your help!

Nayana KCan you please post the final formulas which are in working condition. So that I will try to modify and share here.

leah brooks 11Numerical Time Open

proxyMinutes
Once again, thank you for all of your help!

Nayana Kleah brooks 11That did it! Thank you so much for your help Nayana! :)

Nayana KMost welcome :) Mars Rover 570Hello ,

I have simillar requirement to display HH:MM .

I have creted number formula which is giving correct result . Now the Problem is when I try to replace "." to "HH" and add "MM" that time I am loosing "0" in TEXT formula

SUBSTITUTE(TEXT(Time__c), ".", " HH : ") + " MM"

other than SUBSTITUTE is there any formula to get the "0"(0.40 as 0.40 , 0.05 as 0.05) and rplace "." to "HH"