 ShowAll Questionssorted byDate Posted cormant_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. CRMGeneralist

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

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 hhuie

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