Date Function in TM1: Syntax and Use

The DATE function in TM1 converts a serial date to a text version of the date in either YYYY-MM-DD or YY-MM-DD format.

For example, let’s say you have a date of 9-Mar-98. The serial number representing this date (using the TM1 date system) is 13947. If you have a simple TM1 element (rather than a string element) with the value 13947 entered and have the element formatted to display a date (such as c:dd-mmm-yy), then the value will be displayed by TM1 as a date, rather than a value. It will still be stored as a value, but displayed as a date.

If you then want to store the date as a string, you need to use the DATE function.

Syntax of the DATE Function

The syntax is:

Date (SerialNumber, FourDigitYear); where:

  • SerialNumber represents the numeric value stored (which could be displayed as a date (the 13947 above)
  • FourDigitYear is an optional boolean value of  either 0 or 1, where 0 tells TM1 to store a 2 digit year and 1 tells TM1 to store a 4 digit year. Also, if it is omitted, then it assumed to be a 2 digit year.

Example

Date(13947 , 1); will return the string ‘1998-03-09’

The Date Function can be then combined with other functions in TM1, like Month, to extract the month value from the string representation of a date.

Usage

Ensure when using the Date function that you are aware of the serial date system used by your TM1 model. There is a parameter in TM1s.cfg that can be set to force TM1 to use the Excel date system. In this instance, 1-Jan-1900 would be 1, whilst wihtout it, 1-Jan-1960 woudl be 1. Mroe infomration on this parameter can be found here.

This function can be used in both Rules and TI Processes.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This entry was posted in Rule Functions, TI Functions and tagged Date. Bookmark the permalink.