Non-Ugly Way Convert a MUMPS $H formatted date/time to an Excel 2016 date/time

Question

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?

score 1 · Accepted Answer · answered Nov 13 '15 at 02:17

1

How about:

=LEFT(A1,FIND(",",A1)-1)-21548+ MID(A1,FIND(",",A1)+1,99)/86400

answered Nov 13 '15 at 02:17

Ron Rosenfeld

53,870
7
28
60

Thanks much. Works well. That and choose the following date format: – Raymond Subasic Nov 16 '15 at 16:46
That and choose the following date format in Excel 2016 "Format Cells:-->Number (tab)-->Category-->Date--Type:": Date: 3/14/15 13:30 – Raymond Subasic Nov 16 '15 at 16:52

score 0 · Answer 2 · edited Jan 30 '16 at 14:51

0

Just curious to know if you are able to use $zdt($h) instead of $h on the Caché side. This will do that exact same conversion for you.

Furthermore, if this is a solution, then there are extra parameters to specify the format. The one you asked about is the default: $zdt($h,1,1) or simply $zdt($h).

$zdt has date formats between 1-15 and time formats between 1-10.

This format is as follows:

$zdt([n],[d],[t])

Where n is a mumps formatted number, d is the date format 1-15 and t is the time format 1-10.

edited Jan 30 '16 at 14:51

MWiesner

8,868
11
36
70

answered Jan 30 '16 at 14:22

Mike M

21
2

Thanks, that's good information on the Cache/Intersystems side of things. I was looking specifically for the $H format as it is stock standard for all MUMPS implementations. – Raymond Subasic Dec 14 '21 at 17:41

Non-Ugly Way Convert a MUMPS $H formatted date/time to an Excel 2016 date/time

2 Answers2