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
cormant_aacormant_aa 

Convert datetime to milliseconds on a formula

Hi.

 

I am trying to convert a datetime value to milliseconds on a formula I making. Anyone has done it or any ideas? Much appreciate the help and thanks.

 

CRMGeneralistCRMGeneralist

No, I wanted to convert just to include seconds and told it could not be done. If anyone figures that out I would be extremely interested, thanks.
hhuiehhuie

Are you converting the Time in the Date Time into Milliseconds?  I have an idea but need more clairifcation before I can test my theory out.

 

Thanks
hhuiehhuie

Not sure if anyone is still intreasted in a solution for this.  I was giving a task of converting a total time (Closed - Open) for cases.  This is formula (Text) I created:

 

TEXT(FLOOR( Time_Resolved_Hrs__c *24)) & ":" &

TEXT(FLOOR(MOD(Time_Resolved_Hrs__c ,1)*60)) & ":" &

TEXT(FLOOR(MOD(Time_Resolved_Hrs__c *1440,1)*60))

 

So basically the format is hh:mm:ss

 

If you need to convert a Date Time into a number you can create a field that is Date Time and enter 1/1/1900 00:00:00 as the starting value but I had problems because it was off by either a day or 12 hours when I converted it.  Hope this helps.
rene.goergensrene.goergens

Hi,

 

I have tried with the above and have found this one to be more correct:

 

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

 

And this one should give the milliseconds as well: (topic of thread):

 

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

René
Anamika Rathore 4Anamika Rathore 4
@rene.goergens

Could You explain a bit more about what your formula is doing? I'm trying to create a formula field to capture milliseconds from a custom dateTime field. I tried with MILLISECOND() function in formula but it does not return milliseconds at all like SF said it would.

Thanks
Mika