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.

Improve this question
2
  • Use a custom format of: yyyy-mm-dd hh:mm:ss.000
    – Bandersnatch
    Commented Oct 11, 2017 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
    Commented Oct 11, 2017 at 14:21

1 Answer 1

Reset to default
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.

Improve this answer
1
  • Thanks a lot, I was not aware of the "DateValue" and "TimeValue" worksheet functions.
    – Dominique
    Commented Oct 12, 2017 at 7:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .