Reference

Top     Installation     Introduction     Samples     Tutorial     Reference     Release Notes

Using Dates & Times in Rules

Dates are expressed in a rule as either DATE(Year, Month, Day) or DATETIME(Year, Month, Day, Hour, Minute, Second). When Excel dates or datetimes are input to a rule set, they are automatically converted to the rule set syntax.

Note that times, without a date, in Excel spreadsheets are not recognized by VBA as dates, and don't get automatically converted when input to a rule set.

Date/time intervals are represented as expressions using the terms: year(s), month(s), day(s), hour(s), minute(s), second(s).

Examples:

cutoff_date = DATE(1988, 8, 28)
change = DATETIME(2000, 2, 16, 13, 30, 0)
age_limit = 16 years 6 months
time_limit = 1 hour 30 minutes

Internally, time intervals are represented by the structure ERA(Years, Months, Days, Hours, Minutes, Seconds). You won't need this directly, but may see it in debugging output.

Be careful with negative quantities when subtracting date/time intervals. An expression with year(s), month(s) etc. is treated as a unit, so a leading minus applies to all the units. So today - 25 years - 1 day is treated as today - (25 years - 1 day). Use of explicit parentheses is recommend in these cases.

Date/times and intervals can be used in expressions in rules. Two special functions can also be used:

• TODAY() — the current date
• NOW() — the current date and time

Examples of date/times in rules:

license = Student WHEN age > 16 years 6 months
expiration_date = start_date + 9 months
age = today - birthday

There are also a number of date and time functions that can be used in rules, for example DAY(), WEEKDAY(), MONTH() and YEAR().

Copyright ©2005-7 Amzi! inc. All Rights Reserved. ARulesXL and Amzi! are trademarks or registered trademarks of Amzi! Microsoft and Excel are trademarks or registered trademarks of Microsoft Corporation.