Implementing Date Functions

In the eFashion.unx universe, there is no object for number of years trading. The universe only has the Opening Date dimension object.

As you can see in the figure, the values returned by the Opening Date object are displayed in the format MM/dd/yy hh:mm:ss a.

To display the number of years that the stores have been trading, create a variable based on the opening date of each store.

If you point the cursor at the Opening Date object in the Document Dictionary tab, you see that it is a string.

To calculate the trading years, remove the time element from the string, and then convert it to a date type. Doing this allows you to perform a date calculation to determine the difference between today's date and the opening date.

You can use the Substr() function to remove the time element of the string. However, the date element is not in a constant format. For example, some days and months are expressed as single digits while others are two digits.

Use the Pos() function to define the number of character elements of the Substr() syntax. The Pos() function is effective because the date is always followed by a space. To identify the position of the space, create the Date_Substring variable.

When the time element is no longer present in the Opening Date object, create another variable to convert the Date_Substring values to date type using the Open_Date variable.

Formulas for Example Variables

This table shows the formulas used in this concept and describes their purpose:

Variable Name	Formula	Purpose
Date_Substring	=Substr([Opening date];1;Pos([Opening date];"")-1)	To remove the time element from the Opening Date column
Open_Date	=ToDate([Date_Substring];"MM/dd/yy")	To convert the Date_Substring values to date type

The date must be formatted in the same way as the string that is being converted. For instance, if the order of the dates in the string is Month/Day, then the date must be formatted the same way. If the order of the dates in the string is Day/Month, however, then the formula must use that format as well.

Now that you have created a date type variable for the store opening date, you can calculate the number of days between opening and today's date by using the CurrentDate() and DaysBetween() functions.

Use the following formula to create a variable called Trading_Years:

=DaysBetween([Open_Date] ; CurrentDate()) / 365.25

To convert the days to years, divide the result by the number of days in a year. Use 365.25 to account for leap years.

If you replace the Open_Date column with the Trading_Years variable, you achieve the required result.

The DaysBetween function can also be used to return the number of periods between two dates, irrespective of time.