DATE (year, month, day)

Description:

This function returns the sequential serial number that represents a particular date. The result is returned as a number.

Syntax: DATE (Year, Month, Day)

• Year : The year argument can be one to four digits. PowerOLAP uses the 1900 Date format by default and it interprets the year argument according to this date system.
• Month : is a number representing the month of the year. If month is greater than 12, it adds that number of months to the first month in the year specified. For example, DATE (2008, 14, 2) returns the serial number representing February 2, 2009.
• Day : is a number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE (2008, 1, 35) returns the serial number representing February 4, 2008.

Remarks

• All arguments are compulsory.
• The DATE function is most useful in formulas where year, month, and day are formulas, not constants.

Example

The DATE function can be used in any database where you have stored values for month, year, and day and want to return the date as a serial number, or where you want to calculate the date by entering the parameters for the date directly from the formula interface. In these cases it is possible to access the serial date with the help of this function. This function displays the value of the date as a number calculated from 1^st January, 1900 which is the base date for the "DATE" function.

=DATE (2002, 07, 22)	-Date converted to serial number counted from 1st January, 1900 till 07/22/2002. (37459).
=DATE ("range reference")	-Date converted to serial number counted from 1st January, 1900 till date specified in the given range.

 

Let us take the example of the HR database in PowerOLAP which has data which that gives the information in numbers for the day of the year, the month and the date and these are the parameters for which we calculate the "DATE' value. In this case we can write a formula in the appropriate cube in PowerOLAP to pull in values from the members that form the parameters for the function and write them to the formula and use the numbers to derive the values for the another member of the dimension namely, "Serial Date" and apart from this example you can even do this for a different dimension in a different cube.



The formula above writes values into the "Staff Date Lookup" cube to the "Staff Date Lookup" dimension into the "Serial Date" member by calculating the number for the "Date" with the "DATE" function using the parameters of year, month and day, which are members in the same dimension in the same cube. The DATE function takes the "Year", "Month" and "Day" parameters, converts the combined value to a date and returns it as a number calculated from 1^st January, 1900 till the date which the function determines from the parameters.

Example: A date of November 11, 2011 will return a value of 40858 as the  Serial Date value.