excel number formatting to show time in milliseconds

[omairhe]

Hey everyone,

How can excel number formatting format the =Now() function to show the time in milliseconds, this means that the date and time will be
shown as milliseconds in a cell.


Wed Mar 6 2019 20:16:17
equals
1551885377803 milliseconds

Will appreciate and thank you.

 

[Special-K99]

Does it work if you multiply the date by 35650247.03 ?

 

[omairhe]

Does it work if you multiply the date by 35650247.03 ?

Not exactly . I want the actual milliseconds to be extracted from Now formula first . as these will add up to the final integer.

 

[joeu2004]

First, Excel NOW is accurate only to 1/100 second (truncated).

And VBA Now is accurate to only 1 second (truncated).

Do you really want current time accurate to 1/1000?

Second (no pun intended), =86400000*NOW() converts current date and time to milliseconds.

But it would be prudent to write =ROUND(86400000*NOW(),0) in order to eliminate binary arithmetic anomalies (extra unseen precision).

However, Mar 6 2019 20:16:17.803 is 3761064977803 milliseconds, not 1551885377803.

I think you want so-called Unix Time, aka Coordinated Universal Time (UTC).

That would be =86400000*(NOW() - DATE(1970,1,1)), wrapped with ROUND(...,0) for good measure.

To convert UTC to Excel time, use =1551885377803/86400000 + DATE(1970,1,1), formatted as m/d/yyyy h:m:s.000 . Note that that is Mar 6 2019 15:16:17.803, not 20:16:17.803.

 

[omairhe]

this is working for my need =86400000*NOW()


thanks,
much appreciated...