How to Convert a String Date to a Numeric Date in using the DayNo TM1 Function

Here we are going to explain how to use the DAYNO TM1 function to convert a date as a string into a serial number. For example, to convert 14-Apr-2016 into the serial number 20558. This is the opposite of the DATE function, which converts from a numeric date to a string.

A bit of background though. Yesterday I was doing some work on an inventory forecasting system.  I had Year, Month and Day dimensions and needed to bring through Actuals for all days up until yesterday. I have a System Settings cube with Current Day defined in it using a rule. To bring through the Actuals I created a rule that uses the DayNo function and then does some comparisons.

Syntax of DayNo

DAYNO('DateString')

So here if you insert a string in the form YY-MM-DD or YYYY-MM-DD then the rule will evaluate that to the internal TM1 number that corresponds to that day (much like Excel does, but TM1 uses a different start day to Excel – TM1 uses 1-Jan-1960 and Excel uses the start of the 20th century).

Example

For example, if we have DayNo(‘2016-04-14’), then TM1 will return the number 20558. With this you can then add or compare other day numbers easily.

Note that the syntax of the date is absolutely vital.  It will not work with any other syntax than YY-MM-DD or YYYY-MM-DD. Thus you can’t use 02-28-2016 (MM-DD-YYYY) or 2016-Feb-28 (YYYY-MMM-DD). Importantly, you will not get an error, rather it will just not calculate.

Use of This Function to Convert a Text Date to a Serial Number

In the model referred to above, I used it in a rule in the following way:

IF ( DayNo (  !Year | '-' | ATTRS ( 'Month',!Month,'MonthNum') | '-' | !Day )  <= DayNo ( DB ( 'System Settings', 'Current Date', 'String' ) ) , ['Actual'] , Continue );

Here I am getting the evaluated values for !Year, !Month and !Day and converting them to a DayNo, then testing that against the DayNo from the System Settings cube using a DB function. If it is true (i.e. less than today), then I am getting the Actual value and otherwise Continuing. There’s an ATTRS in there because the Month dimension uses there character months (like ‘Apr’), rather than ’04’. The attribute MonthNum has the ’04’ in it.

This applies to both Rules and Turbo Integrator processes.