# excel number formatting to show time in milliseconds

#### omairhe

##### Well-known Member
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Does it work if you multiply the date by 35650247.03 ?

Last edited:
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.

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.

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

thanks,
much appreciated...

Replies
2
Views
114
Replies
3
Views
79
Replies
9
Views
274
Replies
12
Views
277
Replies
0
Views
115

Threads
1,203,534
Messages
6,055,954
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back