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. |