Spreadsheet Formulas - Date & Time Functions

Statistica provides a predefined broad selection of date and time functions that can be used in spreadsheet formulas like all other functions.

For all functions that have a year parameter, 2 digit years are accepted and Statistica will interpret them as specified on Tools/Options/Spreadsheets tab. In other words, a function's year parameter will be interpreted in same manner that spreadsheets interpret years.

When a function returns a Julian date, the returned value is actually a number (the same format that Statistica uses for internal date representation). It is a floating point number where the integer part is number of days since 1/1/1900 and the fractional part is time. If the variable is formatted as a date or time, it will be displayed in that user-defined format. See also, How are dates represented in Statistica data files? and How is time represented in Statistica data files?.

Below is a list of all available spreadsheet functions (parameters are given in parentheses).
 

GetDate(string)

Convert date value to Julian date; can be constant date string, or a text variable which contains a date string

GetTime(string)

Convert time string to Julian date; can be constant time string, or a text variable which contains a time string

DTToday

Returns a Julian date for today. There will be no fractional part, so time is equivalent to 12:00am.

DTDay(date)

Accepts a Julian date and returns its day of month (1-31). For example:

Today is March 23, 2006

DTDay(DTToday)returns 23

DTDay(DTNow)returns 23

DTMonth(date)

Accepts a Julian date and returns its month (1-12). For example:

Today is March 23, 2006

DTMonth(DTToday)returns 3

DTMonth(DTNow)returns 3

DTYear(date)

Accepts a Julian date and returns its year. For example:

Today is March 23, 2006

DTYear(DTNow)returns 2006

DTYear(DTToday)returns 2006

DTHour(date)

Accepts a Julian date and returns its hour (in 24 hour time format). For example:

It is March 23, 2006 at 1:15pm

DTHour(DTNow)returns 13

DTHour(DTToday)returns 0

DTMinute(date)

Accepts a Julian date and returns its minutes (0 to 59). For example:

It is March 23, 2006 at 1:15pm

DTMinute(DTNow)returns 15

DTMinute(DTToday)returns 0

DTSecond(date)

Accepts a Julian date and returns its seconds (0 to 59). For example:

It is March 23, 2006 at 1:15:32pm

DTSeconds(DTNow)returns 32

DTSeconds(DTToday)returns 0

DTDayOfWeek(date, [ type])

Accepts a Julian date and returns its week of year (1 to 366). The first parameter is the Julian date and second parameter is an integer (1, 2, or 3) which represents the week format.

For type 1, 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday.

For type 2, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday.

For type 3, 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday.

For example:

It is Thursday, March 23, 2006 at 1:15:32pm

DTDayOfWeek(DTNow) returns 5

DTDayOfWeek(DTNow, 2) returns 4

DTDayOfWeek(DTToday) returns 5

DTDayOfYear(date)

Accepts a Julian date and returns its numeric day of year (1 to 366). For example:

It is Jan 2, 2006

DTDayOfYear(DTNow) returns 2

DTDayOfYear(DTToday) returns 2

DTWeekOfYear(date [, type])

Accepts a Julian date and returns its week of year (1 to 53)

If type = 1, subsequent weeks begin on Sundays.

If type = 2, subsequent weeks begin on Mondays.

If type = 3, weeks are defined according to ISO 8601, where week 1 of a given year is the one that includes the first Thursday of that year (or, equivalently, week 1 is the week that includes January 4).

Note that the first week may have from 1 to 7 days depending which day Jan 1 falls on.

Examples:

Jan 1, 2004 fell on a Thursday

DTWeekOfYear(#1/1/2004#) returns 1

DTWeekOfYear(#1/3/2004#) returns 1

DTWeekOfYear(#1/4/2004#) returns 2 (Sun starts a new week)

DTWeekOfYear(#1/4/2004#, 2) returns 1 (Sun in prev week)

DTWeekOfYear(#1/5/2004#, 2) returns 2 (Mon starts new week)

DTWeekOfYear(#1/5/2004#, 3) returns 1 (ISO 8601, week 1 is first week that includes Thursday)

DTLongDate(date, [include_time])

Accepts a Julian date and returns it in "long date" (Windows Regional Settings) format. The first parameter is the Julian date and second parameter is an optional Boolean parameter that will include the time if true.

DTShortDate(date, [include_time])

Accepts a Julian date and returns it in "short date" (Windows Regional Settings) format. The first parameter is the Julian date and second parameter is an optional Boolean parameter that will include the time if true.

DTDate(year, month, day, [hour], [min], [second])

Accepts a Julian date and returns it as a double.

The year parameter is required, and is an integer. A 2 digit year will be converted according to the Tools/Options/Spreadsheet tab setting.

The month is an integer, and is the ordinal month, 1 (January) through 12 (December).

The day is an integer, and the day month, 1 through 31.

The hour is optional, and if provided, is an integer 24 hour value, 0 through 23.

The minute is optional, and if provided, is an integer 0 through 59.

The second is optional, and if provided, is a double number in the range of 0.0 through 59.99999999.

DTEndOfMonth(date)

When passed a Julian date, DTEndOfMonth takes the month/day/year, and returns the Julian date for midnight on last day of the same month

For example: Today is March 23, 2006

DTEndOfMonth(DTNow) returns March 31, 2006  00:00:00

DTEndOfNextMonth(date)

When passed a Julian date, DTEndOfNextMonth takes the month/day/year and returns the Julian date for midnight on the last day of the next month

For example: Today is March 23, 2006

DTEndOfNextMonth(DTNow) returns April 30, 2006  00:00:00

DTEndOfQuarter(date)

When passed a Julian date, DTEndOfQuarter takes the month/day/year and returns the Julian date for midnight on the last day of the same quarter

For example: Today is February 23, 2006

DTEndOfQuarter(DTNow) returns March 31, 2006  00:00:00

DTAddYears(Date, NumberYearsToAdd)

When passed a Julian date a number of months, DTAddYears takes the month/day/year and returns the Julian date after adding the passed number of years. The time portion remains the same as in the initial date. The day of the new date tries to remain the same as the passed date, but if the passed date was February 29 in a leap year and the resulting year is not a leap year, then the returned date will be the February 28 of the resulting year.

Note that the number of years to add will be truncated to an integral value; e.g., 1.9 will be interpreted as 1.0.

Also, the number of years can be negative, which will subtract the number of years from the current date.

For example: Today is February 23, 2006 10:23:42

DTAddYears(DTNow,3) returns February 23, 2009  10:23:42

DTAddMonths(Date, NumberMonthsToAdd)

When passed a Julian date a number of months, DTAddMonths takes the month/day/year and returns the Julian date after adding the passed number of months. The time portion remains the same as in the initial date. The day of the new date tries to remain the same as the passed date, but if the month in the resulting date does not have that many days, then the returned date will be the last day of that month.

Note that the number of months to add will be truncated to an integral value; e.g., 1.9 will be interpreted as 1.0.

Also, the number of months can be negative, which will subtract the number of months from the current date.

For example: Today is February 23, 2006 10:23:42

DTAddMonths(DTNow,2) returns April 23, 2006  10:23:42

DMSToDecimal (degrees, minutes, seconds)

DMSToDecimal will take the passed degrees, minutes, and seconds, and return as decimal degrees.

The passed degrees/minutes/seconds values are floating point values; you could pass 10.5 as "Degrees", and the resulting calculations will be treated as if you had specified an additional 30 minutes

The passed minutes/second values can be more than 60; for example, if 62 minutes are passed, the result will be the same as if an extra degree was added with 2 minutes.

The conversion can return values greater than 360 if the component parts result in that.

For example:

The measurement of 36° 9' 14" can be passed as:

DMSToDecimal(36,9,14) and returns 36.1538889

ATAN2(y,x)

ATAN2 is a variation of the arctangent function. For any real arguments x and y not both equal to zero, atan2(y, x) is the angle in radians between the positive x-axis of a plane and the point given by the coordinates (x, y) on it. The angle is positive for counter-clockwise angles (upper half-plane, y > 0), and negative for clockwise angles (lower half-plane, y < 0).

For example:

ATAN2(20, 30) returns 0.588003

Haversine(lat1, lon1, lat2, lon2

[, unitOption])

The Haversine returns the great-circle distance between two points on a sphere given their longitudes and latitudes. The two points are passed as (lat1, lon1), and (lat2, lon2).

The optional unitOption controls the units of the returned value:

unitOption     units

1                  kilometers (default if no unitOption used)

2                  statute miles

3                  nautical miles

For example:

Haversine(10, 20, 30, 40) returns 3040.603 kilometers.

Haversine(10, 20, 30, 40, 2) returns 1889.343 statute miles.

Haversine(10, 20, 30, 40, 3) returns 1641.794 nautical miles.

See also: Distributions and their Functions, Spreadsheet Formulas - Overview, Spreadsheet Formulas - Syntax Summary, Spreadsheet Formulas - Examples, Spreadsheet Formulas - Syntax Operators, Spreadsheet Formulas - Distributions and their Functions.