Reverse cell formatting, how to explain a certain datetime format to Excel

I am aware that Excel can modify the appearance of a cell content, using "Format cells". When I enter "20000" in a cell, and I use "Format cells", more exactly "Date", Type "14/03/2012", I see "03/10/1954".

Now I would like to do just the opposite: I have quite a lot of cells, containing datetime stamps like "2017-10-09 01:56:39.466", and I would like to explain to Excel that this is a datetime format, to be interpreted as `YYYY-MM-DD hh:mm:ss.ttt` (where `t` stands for "thousands of seconds"). Is that possible?

The idea behind it is that I have a lot of those cells, and I would like to substract those datetime stamps in order to find out which was the entry with the longest duration, but as far as I know, substraction is only possible by number values, hence the need to make Excel understand the used format.

I'm working with Microsoft Excel 2016 MSO 32-bit.

• Use a custom format of: yyyy-mm-dd hh:mm:ss.000 – Bandersnatch Oct 11 '17 at 14:21
• But Excel understands subtraction of date/time values. The underlying value of a date is just the number of days since Jan 1, 1900 - which you can see by formatting the cell as a number. If you subtract two dates and format the result as a number, you get the number of days between the two dates. The decimal part of the number is the fractional number of days, which could extend out far enough to represent milliseconds. – Bandersnatch Oct 11 '17 at 14:21

``````=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))+RIGHT(A1,3)/(24*60*60*1000) 