=date(); function

Outline

The =date(); Scripting Engine function is a function that takes static or variable input and returns a date as a ISO date, Unix date (number of seconds from January the 1st. 1970) or any valid custom format.

Aliases

=date();
=d();

Properties

The date function has complex input:

Property Datatype Default value Description
mask String RdayS Name of the datefunction to be used.
See section below for options
offset Integer 0 Offset used relative with the mask/datefunction
output String I

Output format which identifies the format you want the date to be formatted:
I - ISO Date
U - Unix Date (num seconds since 1970-01-01)

[CUSTOM] - Set .net date format

startofyear String 01-01 Identify the start day of year (MM-DD) to be used if you want to set your own fiscal year

Examples

=date({"mask":"RmonthS"});
=date({"mask":"RmonthS","offset":0});
=date({"mask":"RmonthS","offset":0,"output":"U"});
=date({"mask":"RmonthS","offset":0,"output":"d. MMM yy"});
=date({"mask":"RyearS","offset":0,"output":"I","startofyear":"10-01"});
=date({"mask":"RyearS","offset":0,"output":"I","startofyear":"=sv(fiscalyear_start);"});

Fiscal years

The AnalyzePRO platform supports fiscal years through use of the startofyear property.

There is a System Variable named "fiscalyear_start" defined in the platform.
This variable has default value "10-01" which translates to October the 1st. 
If you want to create fiscal periods, the first thing you should do is to set this variable to your start of fiscal year. Once the fiscal year start variable is set, you can reference this variable as your startofyear value in the fiscal periods.

Mask property: Available Date masks

The mask property is the main input property of the =date(); function. It controls which date function to be used.

There are basically three distinct types of date masks:

1 Absolute date masks
Calculate dates based on a static mask and a dynamic year controlled by the offset property.

2 Relative to current date masks
Calculates a date based on a mask relative to the current date

3 Relative to current year date masks
Calculates a date based on a ordinal period of current year

The plaform supports the following date masks:

Absolute dates

Common to all absolute dates:
Property "offset" controls which year. 
Offset < 1000 identifies relative year
Offset > 1000 identifies year

Weeks

mask Description Example
week1S Start of first week.
Uses offset input to control which year
=d("mask":"week1S"); // start of first week current year (offset: 0)
=d("mask":"week1S", "offset": 2011); // start of first week 2011
week1E End of first week.
Uses offset input to control which year
=d("mask":"week1E"); // end of first week current year (offset: 0)
=d("mask":"week1E", "offset": 2011); // end of first week 2011
... ...
week52S Start of 52nd week
week52E End of 52nd week
week53S Start of 53rd week
If that year does not include 53 weeks it will return the frist week the year after
week53E Start of 53rd week
If that year does not include 53 weeks it will return the frist week the year after

Months

mask Description Example
month1S Start of first month =d("mask":"month1S"); // start of first quarter current year (offset: 0)
=d("mask":"month1S", "offset": 2011); // start of first quarter 2011
month1E End of first month =d("mask":"month1E"); // end of first quarter current year (offset: 0)
=d("mask":"month1E", "offset": 2011); // end of first quarter 2011
... ...
month12S Start of 12th month
month12E End of 12th month

Quarters

 

mask Description Example
quarter1S Start of first quarter =d("mask":"quarter1S"); // start of first month current year (offset: 0)
=d("mask":"quarter1S", "offset": 2011); // start of first month2011
quarter1E End of first quarter =d("mask":"quarter1E"); // end of first month current year (offset: 0)
=d("mask":"quarter1E", "offset": 2011); // end of first month 2011
... ...
quarter4S Start of 4th quarter
quarter4E End of 4th quarter

 

Halfyears

 

mask Description Example
halfyear1S Start of first halfyear
(start of year)
=d("mask":"halfyear1S"); // start of first halfyear current year (offset: 0)
=d("mask":"halfyear1S", "offset": 2011); // start of first halfyear 2011
halfyear1E End of first halfyear =d("mask":"halfyear1E"); // end of first halfyear current year (offset: 0)
=d("mask":"halfyear1E", "offset": 2011); // end of first halfyear 2011
halfyear2S Start of 2nd halfyear
halfyear2E End of 2nd halfyear (end of year)

 

Years

 

mask Description Example
yearS Start of year =d("mask":"yearS"); // start of year current year (offset: 0)
=d("mask":"yearS", "offset": 2011); // start of year 2011
yearE End of year =d("mask":"yearE"); // end of year current year (offset: 0)
=d("mask":"yearE", "offset": 2011); // end of year 2011

 

Relative to current date

 

mask Description Example
RdayS Start of today =d("mask":"RdayS"); // start of today (offset: 0)
=d("mask":"RdayS", "offset": 1); // start of tomorrow
RdayE End of today =d("mask":"RdayE"); // end of today (offset: 0)
=d("mask":"RdayE", "offset": 1); // end of tomorrow
RweekS Start of week =d("mask":"RweekS"); // start of current week (offset: 0)
=d("mask":"RweekS", "offset": 1); // start of next week
RweekE End of week =d("mask":"RweekE"); // end of current week (offset: 0)
=d("mask":"RweekE", "offset": 1); // end of next week
RmonthS Start of month =d("mask":"RmonthS"); // start of current month(offset: 0)
=d("mask":"RmonthS", "offset": 1); // start of next month
RmonthE End of month =d("mask":"RmonthE"); // end of current month(offset: 0)
=d("mask":"RmonthE", "offset": 1); // end of next month
RquarterS Start of quarter =d("mask":"RquarterS"); // start of current quarter (offset: 0)
=d("mask":"RquarterS", "offset": 1); // start of next quarter
RquarterE End of quarter =d("mask":"RquarterE"); // end of current quarter (offset: 0)
=d("mask":"RquarterE", "offset": 1); // end of next quarter
RhalfyearS Start of halfyear =d("mask":"RhalfyearS"); // start of current halfyear (offset: 0)
=d("mask":"RhalfyearS", "offset": 1); // start of next halfyear
RhalfyearE End of halfyear =d("mask":"RhalfyearE"); // end of current halfyear (offset: 0)
=d("mask":"RhalfyearE", "offset": 1); // end of next halfyear
RyearS Start of year =d("mask":"RyearS"); // start of this year (offset: 0)
=d("mask":"RyearS", "offset": 1); // start of next year
RyearE End of year =d("mask":"RyearE"); // end of this year (offset: 0)
=d("mask":"RyearE", "offset": 1); // end of next year

 

Relative to current year

offset = 0 defaults to 1 which is the first period of the year

mask Description Example
CYdayS Start of given day in current year =d("mask":"CYdayS"); // start of first day of year (offset: 0=1)
=d("mask":"CYdayS", "offset": 10); // start of 10th day of year
CYdayE End of given day in current year =d("mask":"CYdayE"); // end of first day of year (offset: 0=1)
=d("mask":"CYdayE", "offset": 10); // end of 10th day of year
CYweekS Start of given week in current year =d("mask":"CYweekS"); // start of first week of year (offset: 0=1)
=d("mask":"CYweekS", "offset": 10); // start of 10th week of year
CYweekE End of given week in current year =d("mask":"CYweekE"); // end of first week of year (offset: 0=1)
=d("mask":"CYweekE", "offset": 10); // end of 10th week of year
CYmonthS Start of given month in current year =d("mask":"CYmonthS"); // start of first month of year (offset: 0=1)
=d("mask":"CYmonthS", "offset": 10); // start of 10th month of year
CYmonthE End of given month in current year =d("mask":"CYmonthE"); // end of first month of year (offset: 0=1)
=d("mask":"CYmonthE", "offset": 10); // end of 10th month of year
CYquarterS Start of given quarter in current year =d("mask":"CYquarterS"); // start of first quarter of year (offset: 0=1)
=d("mask":"CYquarterS", "offset": 3); // start of  3rd quarter of year
CYquarterE End of given quarter in current year =d("mask":"CYquarterE"); // end of first quarter of year (offset: 0=1)
=d("mask":"CYquarterE", "offset": 3); // end of  3rd quarter of year
CYhalfyearS Start of given halfyear in current year =d("mask":"CYhalfyearS"); // start of first halfyear of year (offset: 0=1)
=d("mask":"CYhalfyearS", "offset": 2); // start of 2nd day of year
CYhalfyearE End of given halfyear in current year =d("mask":"CYhalfyearE"); // end of first halfyear of year (offset: 0=1)
=d("mask":"CYhalfyearE", "offset": 2); // end of 2nd day of year

Offset property

The offset property controls relative offset period or year. See the different mask options for how to apply this property.

Output property

This property controls the dateformat the function will return. Options are I - ISO Date (Default), U - Unix date or [CustomFormat].

For custom formatting, use standard ms.net date formats

Startofyear property

This is a property that identifies the start date of year. Default (or if not provided) this is set to "01-01" which translates to January the 1st.

If you need to create fiscal periods, you should use this property. If Fiscal periods are not relevant, you do not need to use this property.

 

Refer the Scripting Engine article for other functions