+ Start a Discussion
leah brooks 11leah 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:
 
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.
Best Answer chosen by leah brooks 11
Nayana KNayana K
IF(ISBLANK(Complete_Date_Time__c ) , NULL,VALUE(TEXT( FLOOR( MOD( Case_Open_Length__c* 24, 24 ) ) +(FLOOR(Case_Open_Length__c)*24)) & "." 
& IF(LEN(proxyMinutes__c) = 1,"0"+proxyMinutes__c, proxyMinutes__c)) 
)



Try this

All Answers

Nayana KNayana K
IF(SomeFormula__c != "", TRIM(LEFT(SomeFormula__c, FIND("days", SomeFormula__c)-1)) + ":" + TRIM(RIGHT(LEFT(SomeFormula__c,FIND("hours", SomeFormula__c)-1),2)) + ":"  + TRIM(RIGHT(LEFT(SomeFormula__c,FIND("minutes", SomeFormula__c)-1),2)), "")

Replace SomeFormula__c with text formula field which you mentioned and check if this works. 
leah brooks 11leah brooks 11
That 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 11leah brooks 11
UPDATE:  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.
 
Value(IF(Time_Open__c != "", TRIM(LEFT(Time_Open__c, FIND("days", Time_Open__c)-1)) + ":" + 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)), ""))

 
Nayana KNayana K
00: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 11leah brooks 11
What 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 KNayana K
Yes, 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 11leah brooks 11
I 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 KNayana K
IF(ISBLANK(Complete_Date_Time__c ) , NULL,VALUE(TEXT( FLOOR( MOD( Case_Open_Length__c* 24, 24 ) ) ) & "." 
& TEXT( ROUND( MOD( Case_Open_Length__c * 24 * 60, 60 ), 0 ) )) 
)

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
leah 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 KNayana K
Create a proxy text formula field: say proxyMinutes__c
IF(ISBLANK(Complete_Date_Time__c ) ,"", TEXT( ROUND( MOD( Case_Open_Length__c * 24 * 60, 60 ), 0 ) ))

and replace the intended number formula field with :
IF(ISBLANK(Complete_Date_Time__c ) , NULL,VALUE(TEXT( FLOOR( MOD( Case_Open_Length__c* 24, 24 ) ) ) & "." 
& IF(LEN(proxyMinutes__c) = 1,"0"+proxyMinutes__c,  proxyMinutes__c)) 
)


Number cannot hold 2 decimal points :
12.30.55 is not valid. So you cannot add dd.hh.mm here
leah brooks 11leah brooks 11
I 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 KNayana K
Oh, for less than 10 minutes what value proxy field is returning?
leah brooks 11leah brooks 11
the 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 KNayana K
Can you please post the final formulas  which are in working condition.  So that I will try to modify and share here.
 
leah brooks 11leah brooks 11
Numerical Time Open
IF(ISBLANK(Complete_Date_Time__c ) , NULL,VALUE(TEXT( FLOOR( MOD( Case_Open_Length__c* 24, 24 ) ) ) & "." 
& IF(LEN(proxyMinutes__c) = 1,"0"+proxyMinutes__c, proxyMinutes__c)) 
)
proxyMinutes
IF(ISBLANK(Complete_Date_Time__c ) ,"", TEXT( ROUND( MOD( Case_Open_Length__c * 24 * 60, 60 ), 0 ) ))

 Once again, thank you for all of your help!

 
Nayana KNayana K
IF(ISBLANK(Complete_Date_Time__c ) , NULL,VALUE(TEXT( FLOOR( MOD( Case_Open_Length__c* 24, 24 ) ) +(FLOOR(Case_Open_Length__c)*24)) & "." 
& IF(LEN(proxyMinutes__c) = 1,"0"+proxyMinutes__c, proxyMinutes__c)) 
)



Try this
This was selected as the best answer
leah brooks 11leah brooks 11
That did it!  Thank you so much for your help Nayana! :)

User-added image
Nayana KNayana K
Most welcome :)
Mars Rover 570Mars Rover 570
Hello ,

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"