How to Convert a Text Date-time to a Numeric Element

Do you know how to convert a text date-time into a numeric (or full serial datetime) element in TM1? For example, if you have an Excel file as the original source with a date time formatted from a date serial. In Excel the data displayed as, say, “2021-07-06 06:30” and is stored as 44383.2708333333. However, if the Excel file is then saved as a CSV, it will be saved with the text “2021-07-06 06:30”, not the serial number. If you want to load that into TM1, you need to code it in your TI to convert it into the required serial date.

How to Load a Text Date-time into a Numeric Element

When a Turbo Integrator process reads that text file, it sees “2021-07-06 06:30”, not the serial number. We need to dissect it and reconstruct it as the serial. To do this we need three steps in our TI:

1. Extract the day number using the DAYNO function. This results in the serial number that represents the date portion of the full number. So for “2021-07-06 06:30”, the DAYNO is 44383.
2. Extract the time and convert it into a decimal. This itself needs two parts – the hours, converted into minutes and the minutes themselves. These are then added together and then divided by the number of minutes in a day (1440). For “2021-07-06 06:30”, we would get 0.2708333333, ie the portion that 0630 is through the day.
3. Add these two parts together and we get the serial date that we can add into our TM1 numeric element.

Sample TI Code to Convert a String Datetime into a Serial Date

A sample of the TI code to execute the above is this:

nDate = DayNo ( TRIM ( SUBST ( vDate, 1, 10) ) );
nTime = (StringToNumber ( SUBST ( vDate, 12, 2) ) * 60 + StringToNumber ( SUBST ( vDate, 15, 2) ) ) / 1440;
nDateTime = nDate + nTime;

From there we can apply normal mathematic functions to the numeric date and format it however we need it formatted.

Note that the other functions we are using in the code are TRIM, SUBST and StringToNumber.

Note re Original Formatting

In our source file we were fortunate to have the date in the Excel formatted in the form that the DAYNO function likes to read “2021-07-06” and we have leading zeroes on the month, day, hours and minutes. If your source was not nicely formatted like this, then you would need to construct the date into the format “YYYY-MM-DD” to get DAYNO to work.

Also, we completely acknowledge that we could have simply formatted the date field in Excel as a number, so it appeared and was saved as 44383.2708333333. However, in this instance we did not have control of the source Excel.