There are functions that you can use in your rules to count the number of days, weeks, months or years between two input dates, or the number of seconds, minutes or hours between two times.
Count the number of weekdays between two dates
Count the number of whole days between two dates
Count the number of whole weeks between two dates
Count the number of whole months between two dates
Count the number of whole years between two dates
Count the number of seconds between two times
Count the number of whole minutes between two times
Count the number of whole hours between two times
To count the number of weekdays between two dates, you use the Weekday Count function. The earlier input date is inclusive and the later input date is exclusive. For example:
the number of business days in May 2009 = the number of weekdays (inclusive) between 2009-05-01 and 2009-06-01
would calculate the number of business days in May 2009 to be 21. Note that if the first date in the function is after the second date, then the result will be 0.
To count the number of whole days between two dates, you use one of the day difference functions.
The Day Difference function returns the number of whole days between two dates. This calculation includes only one endpoint. For example:
the number of days in the billing period = DayDifference(2007-12-01, 2007-12-14)
would calculate the number of days in the billing period to be 13.
The Day Difference Inclusive function returns the number of whole days (inclusive) between two dates. This calculation includes both endpoints. For example:
the number of days in the billing period = DayDifferenceInclusive(2007-12-01, 2007-12-14)
would calculate the number of days in the billing period to be 14.
The Day Difference Exclusive function Returns the number of whole days (exclusive) between two dates. This calculation excludes both endpoints. For example:
the number of days in the billing period = DayDifferenceExclusive(2007-12-01, 2007-12-14)
would calculate the number of days in the billing period to be 12.
Date and time values and variables can also be used in these functions.
Note that the order of the two dates (or datetimes) in the function does not affect the result.
To count the number of weeks between two dates, you use one of the Week Difference functions.
The Week Difference function returns the number of whole weeks between two dates. This calculation includes only one endpoint. For example:
the number of weeks until the baby is due = WeekDifference(the current date, the baby's due date)
If the current date is 26/6/2009 and the baby's due date is 25/12/2009, the number of weeks until the baby is due is 26.
The Week Difference Inclusive function returns the number of whole weeks (inclusive) between two dates. This calculation includes both endpoints. For example:
the number of weeks until the baby is due = WeekDifferenceInclusive(the current date, the baby's due date)
If the current date is 26/6/2009 and the baby's due date is 25/12/2009, the number of weeks (inclusive) until the baby is due is 27.
The Week Difference Exclusive function returns the number of whole weeks (exclusive) between two dates. This calculation excludes both endpoints. For example:
the number of weeks until the baby is due = WeekDifferenceExclusive(the current date, the baby's due date)
If the current date is 26/6/2009 and the baby's due date is 25/12/2009, the number of weeks (exclusive) until the baby is due is 25.
Date and time values and variables can also be used in these functions.
Note that the order of the two dates (or datetimes) in the function does not affect the result.
To count the number of months between two dates, you use one of the Month Difference functions.
The Month Difference function returns the number of whole months between two dates. This calculation includes only one endpoint. For example:
the number of monthly repayments remaining = MonthDifference(2008-01-15, the final payment due date)
If the final payment due date is 04/09/2009, the number of monthly repayments remaining is 19.
The Month Difference Inclusive function returns the number of whole months (inclusive) between two dates. This calculation includes both endpoints. For example:
the number of monthly repayments remaining = MonthDifferenceInclusive(2008-01-15, the final payment due date)
If the final payment due date is 04/09/2009, the number of monthly repayments remaining is 20.
The Month Difference Exclusive function returns the number of whole months (exclusive) between two dates. This calculation excludes both endpoints. For example:
the number of monthly repayments remaining = MonthDifferenceExclusive(2008-01-15, the final payment due date)
If the final payment due date is 04/09/2009, the number of monthly repayments remaining is 18.
Date and time values and variables can also be used in these functions.
Note that the order of the two dates (or datetimes) in the function does not affect the result.
To count the number of years between two dates, you use one of the Year Difference functions.
The Year Difference function returns the number of whole years between two dates. This calculation includes only one endpoint. For example:
the person's age = YearDifference(the person's date of birth, the current date)
If the person's date of birth is 31/10/1910 and the current date is 26/06/2009, the person's age is 98.
The Year Difference Inclusive function returns the number of whole years (inclusive) between two dates. This calculation includes both endpoints. For example:
the person's age = YearDifferenceInclusive(the person's date of birth, the current date)
If the person's date of birth is 31/10/1910 and the current date is 26/06/2009, the person's age is 99.
The Year Difference Exclusive function returns the number of whole years (exclusive) between two dates. This calculation excludes both endpoints. For example:
the person's age = YearDifferenceExclusive(the person's date of birth, the current date)
If the person's date of birth is 31/10/1910 and the current date is 26/06/2009, the person's age is 97.
Date and time values and variables can also be used in these functions.
Note that the order of the two dates (or datetimes) in the function does not affect the result.
To count the number of seconds between two times, you use one of the Second Difference functions with date and time inputs.
The Second Difference function returns the number of whole seconds between two datetimes. This calculation includes only one endpoint. For example:
the number of seconds between first place and second place = SecondDifference(the first place time, the second place time)
If the first place time is 2008-06-30 09:31:05 and the second place time is 2008-06-30 09:31:10, then the number of seconds between first place and second place is 5.
The Second Difference Inclusive function returns the number of whole seconds (inclusive) between two datetimes. This calculation includes both endpoints. For example:
the number of seconds between first place and second place = SecondDifferenceInclusive(the first place time, the second place time)
If the first place time is 2008-06-30 09:31:05 and the second place time is 2008-06-30 09:31:10, then the number of seconds (inclusive) between first place and second place is 6.
The Second Difference Exclusive function returns the number of whole seconds (exclusive) between two datetimes. This calculation excludes both endpoints. For example:
the number of seconds between first place and second place = SecondDifferenceExclusive(the first place time, the second place time)
If the first place time is 2008-06-30 09:31:05 and the second place time is 2008-06-30 09:31:10, then the number of seconds (exclusive) between first place and second place is 4.
Note that the order of the two dates (or datetimes) in these functions does not affect the result.
To count the number of whole minutes between two times, you use one of the Minute Difference functions with date and time inputs.
The Minute Difference function returns the number of whole minutes between two datetimes. This calculation includes only one endpoint. For example:
the number of minutes late the plumber is = MinuteDifference(the time the plumber was meant to arrive, the time that the plumber actually arrived)
If the time the plumber was meant to arrive is 2009-10-18 08:30:00 and the time that the plumber actually arrived is 2009-10-18 09:00:40, then the number of minutes late the plumber is is 30.
The Minute Difference Inclusive function returns the number of whole minutes (inclusive) between two datetimes. This calculation includes both endpoints. For example:
the number of minutes late the plumber is = MinuteDifferenceInclusive(the time the plumber was meant to arrive, the time that the plumber actually arrived)
If the time the plumber was meant to arrive is 2009-10-18 08:30:00 and the time that the plumber actually arrived is 2009-10-18 09:00:40, then the number of minutes (inclusive) late the plumber is is 31.
The Minute Difference Exclusive function returns the number of whole minutes (exclusive) between two datetimes. This calculation excludes both endpoints. For example:
the number of minutes late the plumber is = MinuteDifferenceExclusive(the time the plumber was meant to arrive, the time that the plumber actually arrived)
If the time the plumber was meant to arrive is 2009-10-18 08:30:00 and the time that the plumber actually arrived is 2009-10-18 09:00:40, then the number of minutes (exclusive) late the plumber is is 29.
Note that the order of the two dates (or datetimes) in these functions does not affect the result.
To count the number of whole hours between two times, you use one of the Hour Difference functions with date and time inputs.
The Hour Difference function returns the number of whole hours between two datetimes. This calculation includes only one endpoint. For example:
the number of hours the plane was delayed by = HourDifference(the scheduled arrival time of the flight, the arrival time of the delayed flight)
If the scheduled arrival time of the flight is 2006-10-13 09:50:00 and the arrival time of the delayed flight is 2006-10-13 11:00:00, then the number of hours the plane was delayed by is 1.
The Hour Difference Inclusive function returns the number of whole hours (inclusive) between two datetimes. This calculation includes both endpoints. For example:
the number of hours the plane was delayed by = HourDifferenceInclusive(the scheduled arrival time of the flight, the arrival time of the delayed flight)
If the scheduled arrival time of the flight is 2006-10-13 09:50:00 and the arrival time of the delayed flight is 2006-10-13 11:00:00, then the number of hours (inclusive) the plane was delayed by is 2.
The Hour Difference Exclusive function returns the number of whole hours (exclusive) between two datetimes. This calculation excludes both endpoints. For example:
the number of hours the plane was delayed by = HourDifferenceExclusive(the scheduled arrival time of the flight, the arrival time of the delayed flight)
If the scheduled arrival time of the flight is 2006-10-13 09:50:00 and the arrival time of the delayed flight is 2006-10-13 11:00:00, then the number of hours (exclusive) the plane was delayed by is 0.
Note that the order of the two dates (or datetimes) in these functions does not affect the result.
See also: