Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
sfdc007

# 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

☯ BonY ☯

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'))))

Jithin 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.

Thanks!
Abhishek 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.
Hi,
Abhishek,

I am new to sfdc and requesting you  please explain about maps with method  ,when we use .
sfdc007
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 ☯

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 ☯
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"