Count periods between two dates or times

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.

What do you want to do?

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

Count the number of weekdays between two dates

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.

Count the number of whole days between two dates

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.

Count the number of whole weeks between two dates

To count the number of weeks between two dates, you use the Week Difference function. 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. Date and time values and variables can also be used in this function.

Count the number of whole months between two dates

To count the number of months between two dates, you use the Month Difference function. 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. Date and time values and variables can also be used in this function.

Count the number of whole years between two dates

To count the number of years between two dates, you use the Year Difference function. 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. Date and time values and variables can also be used in this function.

Count the number of seconds between two times

To count the number of seconds between two times, you use the Second Difference function with date and time inputs. 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.

Count the number of whole minutes between two times

To count the number of whole minutes between two times, you use the Second Difference function with date and time inputs. 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.

Count the number of whole hours between two times

To count the number of whole hours between two times, you use the Second Difference function with date and time inputs. 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.

 

See also: