Trying to create a formula text field that converts Time Administration (which is in seconds and is a number field) to HH:MM:SS using a text formula. I've read about 100 forums on this and the best formula I've found is this:
IF(ISBLANK(Seconds__c),null,
IF((MOD((Seconds__c)/60,1)*60) > 10,
TEXT(FLOOR( (Seconds__c)/60)) + ":" + TEXT( FLOOR(MOD((Seconds__c)/60,1)*60) ),
TEXT(FLOOR( (Seconds__c)/60)) + ":0" + TEXT( FLOOR(MOD((Seconds__c)/60,1)*60) )
))
The only issue is that when it returns the value it formats it like h:m:s not hh:mm:ss
example: 1hr 2 mins 05 seconds is displayed as 1:2:5 not 01:02:05
Any help with fixing this formula is APPRECIATED! I can live with the current format but would prefer not to.
Hi
IF(ISBLANK(Seconds__c),null,
LPAD(TEXT( FLOOR(Seconds__c /60 / 60)),2,"0") + ":" +
LPAD(TEXT(FLOOR( (Seconds__c)/60) - FLOOR(Seconds__c /60 / 60)*60),2,"0") + ":" +
LPAD(TEXT( FLOOR(MOD((Seconds__c)/60,1)*60)),2,"0")
)
