| DTADDDAYS(date, days)
|
Add the given number of days to the given date
|
| DTADDHOURS(date, hours)
|
Add the given number of hours to the given date
|
| DTADDMINS(date, mins)
|
Add the given number of minutes to the given date
|
| 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: 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
|
| DTADDSECS(date, secs)
|
Add the given number of seconds to the given date
|
| 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: 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
|
| 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.
|
| DTDATEDIFF(datepart, startdate, enddate, [dst_opt])
|
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. datepart can be one of the strings year, quarter, month, dayofyear, day, week, hour, minute, or second. If dst_opt is true (non-zero) and the datepart is hours, minutes, or seconds, then the calculation will adjust for DST boundaries crossed between the two dates using the current/local time zone. If dst_opt is the Windows name of a Time Zone, then the calculation will be adjusted for DST boundaries in that specified time zone.
|
| 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
|
| 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
|
| 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)
|
Return the date of the end of the quarter following the input 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
|
| 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
|
| 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.
|
| 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
|
| 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
|
| DTNOW
|
Return the current date and time as a date value
|
| 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
|
| 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.
|
| DTTIME(hour, minute, second)
|
Construct a time value from hour, minute, and seconds.
|
| DTToday
|
Returns a Julian date for today. There will be no fractional part, so time is equivalent to 12:00am.
|
| 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: 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)
|
| 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
|
| 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
|
| ToNumberEx(x, "0.000")
|
Convert string to number with parser. Format string can be those used by spreadsheet variable formats.
|
| ToStringEx(x, "0.000")
|
Convert number to string with parser. Format string can be those used by spreadsheet variable formats.
|
| 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.
|
