In Excel how do I format cells correctly to work with the following date/time format?-CodePudding

Could someone please tell me what cell format I should use to format the following so that they are recognised in a date time format I can then use to sort from oldest date to newest date please?

02-DEC-21 21.32.01.666000000
02-DEC-21 22.16.50.588000000
03-DEC-21 00.31.06.414000000
03-DEC-21 03.50.11.644000000
03-OCT-21 18.04.41.267000000
04-DEC-21 05.39.27.832000000

I’ve tried using dd-mmm-yy hh.mm.ss.000 (can’t enter more than three 0’s in milliseconds) and also without the .000 at the end but the cells don’t get recognised as dates when I try and sort them

Thanks

CodePudding user response：

You can use two formulas to retrieve the date part and the time part. I named the range with the dates DateColumn

To retrieve the date part: =DATEVALUE(SUBSTITUTE(LEFT(DateColumn,9),".",":"))

To retrieve the time part: =TIMEVALUE(SUBSTITUTE(MID(DateColumn,11,8),".",":"))

To retrieve the whole date incl. time: =DATEVALUE(SUBSTITUTE(LEFT(DateColumn,9),".",":")) TIMEVALUE(SUBSTITUTE(MID(DateColumn,11,8),".",":"))

These formulas return a number, e.g. 44532 for 02-DEC-21 or 0,92835648 for 22.16.50 or 44532,92835648 for the whole date with time. You have to format them accordingly.

But be aware: this will only work on an english system. E.g. on a German system only Dez or Okt would get recognized.