Format date with miliseconds

Hi. I'm a regular user.

I believe the formatting can only take 3 0's

h:mm:ss.000

Hi John M,

I'm Sneha and I'd be happy to help you out with your question.

No, nothing changed recently related to your issue, it occurred because after update Excel got reconfigured, to get it working back as it was working earlier please follow below mentioned steps:-

• Click the File tab.
• Under Help, click Options.
• In the Excel Options dialog box, click the Formulas category.
• Under Error checking rules, clear the Numbers formatted as text or preceded by an apostrophe check box.
• Click OK.

That error message will not affect the working you can disable it by above steps rest it will work as earlier.

I hope this information helps. If you have any questions, please let me know and be glad to assist you further.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

John Max wrote:

I have to import files with a time column which includes four digits of milliseconds. Before I was able to get excel to recognise the field as a date by entering the following custom date format: hh:mm:ss.0000

However, now this does not work. Excel returns an error saying "Microsoft Excel cannot use the number format you've typed.".

Excel for Windows has never allowed more than 3 zeros in the format h:mm:ss.000 . I don't know about Excel for Mac. But I would be surprised if it were different.

However, if even h:mm:ss**.**000 does not work, the problem might be that your system or Excel configuration has changed, and numbers now use comma for the decimal point instead of period.

Check your region/language configuration. Or try entering the time format or any number with a comma for the decimal point. That is, h:mm:ss**,000 as format, or 12,**34 as data.

BTW, although you can import times with more decimal places, note that Excel rounds to the millisecond. To demonstrate:

I formatted B2 as Custom h:mm:ss.000 .

I formatted C2 to display "only" 11 decimal places. If you format it to display 13 decimal places, the most that Excel will format in this case, you will see 12.3459999999969 due to common binary arithmetic anomalies.

Using your repro steps - I get what you got. This is using 16.64 in current channel preview from an E5 subscription.

1. Format A2 as Text, then enter 12:34:12.34567890
2. Format B2 as Custom h:mm:ss.000 (three zeros), then enter the formula =--A2
3. Format C2 as Number with 11 decimal places, then enter the formula =(B2-"12:34")*86400
4. The results should be: 12:34:12.346 in B2 and 12.34600000000 in C2
5. Try to change the format of B2 to Custom h:mm:ss.0000 (four zeros)

Sneha wrote:

No, nothing changed recently related to your issue, it occurred because after update Excel got reconfigured, to get it working back as it was working earlier [....]

That implies that the Custom format h:mm:ss.0000 (four zeros) does indeed work in Excel for Mac.

Please demonstrate that fact by posting an image similar to mine, but formatting B2 as Custom h:mm:ss.0000 (four zeros).

And if that truly works on the Mac (!), please let us know the max number of fractional zeros that Excel for Mac allows in the time format, for my edification.