+ Start a Discussion
sfdc007sfdc007 

Formula Field Logic Help needed

Hi,

I am trying to create a formula field for the below requirement for which i need help on it


I have to convert milli seconds value given to the normal time value using a formula field

let me know the formula field logic to achieve it

Thanks in Advance
Rakesh K KediaRakesh K Kedia
Hi,
You can use the below formula and this will give the milliseconds results as well:

LPAD( TEXT( FLOOR( Duration_Number__c * 24) ), 2, "0" ) & ":" &
LPAD( TEXT( FLOOR( MOD( Duration_Number__c * 24, 1 ) * 60 ) ), 2, "0" ) & ":" &
LPAD( TEXT( FLOOR( MOD( Duration_Number__c * 24 * 60, 1 ) * 60 ) ), 2, "0" ) & "." &
LPAD( TEXT( FLOOR( MOD( Duration_Number__c * 24 * 60 * 60, 1 ) * 1000 ) ), 3, "0" )

Cheers,
Rakesh
 
sfdc007sfdc007
Hi Rakesh,

In the above formula can you tell me which is used for calucalting hrs , min and sec and milli seconds

 
Rakesh K KediaRakesh K Kedia
Hi,

I am not clear on the question. As per my understanding you have a text field in salesforce which have store the time in hh:mm:ss format and now using the formula field you want to convert this time in milisecond. If i am right then you will need to maintain a format in field value storing like 
1. For 10 min 10 second value should stored in field - 00:10:10
2. for 15 second only value should stored in in field -  00:00:15

As per this standard format of field we can calculate the milisecond value after spliting this field in hour, minute and seconds.
sfdc007sfdc007
No

my quesion is

1) i have a number field which is storing the milli seconds value

i have to convert the milli seconds value to hrs: min: sec using a formula field


i tried the below formula

   IF((MOD((Duration_Number__c )/60,1)*60) > 10,

TEXT(FLOOR( (Duration_Number__c )/60)) + ":" + TEXT( FLOOR(MOD((Duration_Number__c )/60,1)*60) ),

TEXT(FLOOR( (Duration_Number__c )/60)) + ":0" + TEXT( FLOOR(MOD((Duration_Number__c )/60,1)*60) )

)



I am getting the value for min and sec correctly

but not able to add hrs to it

let me know how to include hours to it, when i add another condition i am getting incorrect number of parameters in if condition  expected 3 recieved 4

Thanks

 
Rakesh K KediaRakesh K Kedia
Hi,

Please put the below formula in formula type text field. Hope this will work for you.

TEXT(FLOOR(MOD(Time_in_Millisecond__c/(1000*60*60),24))) +"-Hour : "+ TEXT(FLOOR(MOD(Time_in_Millisecond__c/(1000*60),60))) +"-Minute : "+ TEXT(FLOOR(MOD(Time_in_Millisecond__c/(1000),60))) + "-Second"

Thanks & Regards !!
Rakesh