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.
All Answers
Replace SomeFormula__c with text formula field which you mentioned and check if this works.
Thank you for your quick response!!
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)
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.
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! :)
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
Thank you for all of your help!
Once again, thank you for all of your help!
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"