TIMESTRING (date_num, formatStr)

Description:

This function returns the year, the month, the day and the day of the week (text) of a serial date.

Syntax: TIMESTRING (serial number, format_string)

• Serial_number : is the time that contains the year, month, day .
• Format_string : is a parameter that is to be supplied to the function which defines the way the result has to be formatted. The format parameter determines the result of the function, as by this value it is decided whether the serial_number has to be formatted as "day" or "month" or "year".

The different options for this parameter are:
    - %d=day of the Serial Date (in numbers);
    - %b=month of the Serial Date (Abbrv);
    - %B=month of the Serial Date (complete);
    - %y=year of the Serial Date (Abbrv);
    - %Y=year of the Serial Date (complete);
    - %A=day of the week of the Serial Date in words;
    - %a=day of the week of the Serial Date in words (Abbrv)

Remarks

• The serial number argument is compulsory.
• By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
• Serial_number : is a sequential number that represents the date of the day you are trying to find. Dates may be entered as serial numbers (for example, 35825, which represents January 30, 1998 derived using the 1900 date system), or as results of other formulas or functions.

Example

The TIMESTRING function can be used in any database where you have stored values for the number of the serial date and you want to return the year, the month or the day element from that serial date, in this case it's possible to access this value with the help of this function. This function displays the year as a number derived from the computation of the parameter supplied to this function.

=TIMESTRING (29058, %y)

-Returns the year for this date but only the last 2 digits. (The year here is 1979, so the function returns an abbreviated value for the parameter %y, which is the value: "79".

 

Let us take the example of a database in PowerOLAP that needs to access the value for the day, the month and the year from the given Serial Date. All these functions can be accessed from individual PowerOLAP functions or they can be accessed from one function which is "TIMESTRING" by using different parameters.  In this case, we have written a formula in the appropriate cube in PowerOLAP, pulling in the value from the "Serial Date" field and then writing that value to this formula in order to calculate the various results of the "TIMESTRING" function. The formula then calculates the various TIMESTRING elements and the returns the requested information as per the parameter supplied as a string value.



The formulas shown above write values into the "DateData" cube to the dimension "Date Measure" and into the members "Month" ,"Day", "DayOfTheWeek" and the Year" members and all these values are derived from the "Serial Date" member and the function knows which value has to be extracted from the serial date, because the parameters "%B", "%d", "%A" and "%Y", these tell the function what values are to be calculated. The description for all these parameters and more is explained above.