I am looking for a non-ugly way to convert a MUMPS formatted $H ("63868,62327" is 11/12/2015 at 17:18:47) date/time into an Excel 2016 compatible format date/time format. I came up with the following, but it's ugly:
=NUMBERVALUE(NUMBERVALUE(LEFT(A1,(SEARCH(",",A1)-1))-21548) & MID(NUMBERVALUE((1/86400)*MID(A1,(SEARCH(",",A1)+1),5)),2,6))
While this does work, it is definitely ugly. Any ideas?
How about:
=LEFT(A1,FIND(",",A1)-1)-21548+ MID(A1,FIND(",",A1)+1,99)/86400