1

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
0

With your data in cell A1, in B1 enter:

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

and apply the proper format:

enter image description here

B1 appears to the same as A1, but it is numeric.

  • Thanks a lot, I was not aware of the "DateValue" and "TimeValue" worksheet functions. – Dominique Oct 12 '17 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.