How to create and assign a Custom Format of date and time to an entire column in excel worksheet

Question

I want a customized format(with milliseconds also) in such a way to handle for a date and time in Microsoft Excel.

Sample date and time: 08.25.2010 00:52:09.359

For understanding of above sample date time, it is 25th August 2010 12 hrs 52mins 9seconds 359 milliseconds(AM).

Please provide a custom Format to handle the above mentioned sample date and time and finally to apply for a column.

Solution 1

In Excel 2003 I use the following for time with fractions of second:

"hh:mm:ss,000" (note: my decimal point is a "," - German)

so a time entered as 12:13:14,1516 will be displayed as 12:13:14,152

Hope this works for you

Edit 28-Nov-2012

I recently migrated to Excel 2010. Special format using up to 3 zero's after a comma for seconds as described above still works. More than 3 zero's however are not accepted.

Solution 2

I'm not certain what you mean by "not recognizing the dates" but the following custom format would give you the format you're looking for. You'll have to adjust the millisecond separator "." according to your localization, of course.

mm.dd.yyyy hh:mm:ss.000

Applying it to a column is as easy as right clicking the column header, selecting Format Cells, choosing Custom on the left and pasting that format string into the Type box.

If you're trying to enter dates in this format, I don't believe that Excel will support that. I don't know of any option to set the date separators to a "." for Excel. Even setting the date format to use dots under the Regional and Language Options doesn't make Excel recognize that.