function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sfdc007sfdc007 

Formula Field Logic Help needed for time


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


I tried the below formula its working fine for minutes and seconds , but not sure how to add hours to it

MY 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 tried to add hrs but getting incorrect number of parameters in if expected 3 recieved 4

Help me how to add hrs in my formula

Thanks in Advance
Best Answer chosen by sfdc007
☯ BonY ☯☯ BonY ☯
this is helpful for u..


if(((milliseconds__c /1000)<60),'0days/0hrs/0mins/' + text( ((milliseconds__c /1000))) +'sec',
if(and(((milliseconds__c /1000)/60)>0,((milliseconds__c /1000)/60)<60),'0days/0hrs /'+text(floor( ((milliseconds__c /1000)/60))) +'min/'+text(mod((milliseconds__c /1000),60))+'secs',

if(and(((milliseconds__c /1000)/3600)>0,((milliseconds__c /1000)/3600)<24),'0days/'+text(floor( ((milliseconds__c /1000)/3600)))+'hrs/'+text(floor(MOD(((milliseconds__c /1000)/60),60)))+'mins/'+text(mod((milliseconds__c /1000),60))+'secs',


if(((milliseconds__c /1000)/3600)>23,
text(floor(((milliseconds__c/1000)/3600)/24))+'days/'+
text(floor(mod(((milliseconds__c /1000)/3600),24)))
+'hrs/'+text(floor(MOD(((milliseconds__c /1000)/60),60)))+'mins/'+text(mod((milliseconds__c /1000),60))+'secs','nope'))))

All Answers

Jithin Krishnan 2Jithin Krishnan 2
Hi,
Please try this formula. I did some testing and this works. 
IF(LEN(TEXT(FLOOR(MOD(value(Duration_Number__c)/3600000,24))))=2,TEXT(FLOOR(MOD(value(Duration_Number__c) /3600000,24)))&':','0'&TEXT(FLOOR(MOD(value(Duration_Number__c) /3600000,24)))&':')& 

IF(LEN(TEXT(FLOOR(MOD (value(Duration_Number__c) /60000,60))))=2,TEXT(FLOOR(MOD (value(Duration_Number__c) /60000,60)))&':','0'&TEXT(FLOOR(MOD (value(Duration_Number__c) /60000,60)))&':')& 

IF(LEN(TEXT(FLOOR(MOD (value(Duration_Number__c) /1000,60))))=2,TEXT(FLOOR(MOD 
(value(Duration_Number__c) /1000,60))),'0'&TEXT(FLOOR(MOD (value(Duration_Number__c) /1000,60))))

In this formula Duration_Number__c is a text field and the formula return type is also a text field. If yours is a number, you can remove the Value() and Text() methods accordingly. 

Please let me know if you need any help and mark this answer as the best answer if it solved your problem.

Thanks!
Abhishek BansalAbhishek Bansal
Hi,

As per your requirement i have created a formula which will give you the hh:mm::ss format for milliseconds entered

Formula :

If(Duration_Number__c/3600000 > 0,TEXT(FLOOR(Duration_Number__c/3600000)),'00') + ':' +

If(VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10) > 0,TEXT(FLOOR(VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10))),'00') + ':' +

IF(VALUE(TEXT(( VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10) - floor( VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10) ) ) * 100))*(6/10) > 0,TEXT(FLOOR(VALUE(TEXT(( VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10) - floor( VALUE(RIGHT(TEXT(( 1 + Duration_Number__c/3600000 - floor( Duration_Number__c/3600000 ) ) * 100),2))*(6/10) ) ) * 100))*(6/10))),'00')

Enter this formula in your formula field and you will get the desired output.

Regards,
Abhishek.
pradeepkumar battapradeepkumar batta
Hi,
Abhishek,

I am new to sfdc and requesting you  please explain about maps with method  ,when we use .
sfdc007sfdc007
how to calculate the days also in t he above formula along with hrs , min and sec
  
if(((milliseconds__c /1000)<60),'0hr/ 0min /' + text( ((milliseconds__c /1000))) +'sec',
if(and(((milliseconds__c /1000)/60)>0,((milliseconds__c /1000)/60)<60),'0hrs /'+text(floor( ((milliseconds__c /1000)/60))) +'min/'+text(mod((milliseconds__c /1000),60))+'secs',if(((milliseconds__c /1000)/360)>0,text(floor( ((milliseconds__c /1000)/3600)))+'hrs/'+text(floor(MOD(((milliseconds__c /1000)/60),60)))+'mins/'+text(mod((milliseconds__c /1000),60))+'secs','nope')))


 
☯ BonY ☯☯ BonY ☯
this is helpful for u..


if(((milliseconds__c /1000)<60),'0days/0hrs/0mins/' + text( ((milliseconds__c /1000))) +'sec',
if(and(((milliseconds__c /1000)/60)>0,((milliseconds__c /1000)/60)<60),'0days/0hrs /'+text(floor( ((milliseconds__c /1000)/60))) +'min/'+text(mod((milliseconds__c /1000),60))+'secs',

if(and(((milliseconds__c /1000)/3600)>0,((milliseconds__c /1000)/3600)<24),'0days/'+text(floor( ((milliseconds__c /1000)/3600)))+'hrs/'+text(floor(MOD(((milliseconds__c /1000)/60),60)))+'mins/'+text(mod((milliseconds__c /1000),60))+'secs',


if(((milliseconds__c /1000)/3600)>23,
text(floor(((milliseconds__c/1000)/3600)/24))+'days/'+
text(floor(mod(((milliseconds__c /1000)/3600),24)))
+'hrs/'+text(floor(MOD(((milliseconds__c /1000)/60),60)))+'mins/'+text(mod((milliseconds__c /1000),60))+'secs','nope'))))
This was selected as the best answer
☯ BonY ☯☯ BonY ☯
this code also working..
try this

text(FLOOR(milliseconds__c /(1000*60*60*24))) +"days/"+text(FLOOR(MOD(milliseconds__c/(1000*60*60),24))) +"hrs/"+text(FLOOR(MOD(milliseconds__c/(1000*60),60))) +"mins/"+text(FLOOR(MOD(milliseconds__c/(1000),60))) + "secs"