St. Louis ACL User Group

Calculate Working Days Between Two Days

There may be times when you need to determine the number of working days between two dates. This result can be achieved within ACL by creating a conditional computed field involving multiple ACL functions.

Suppose that start_date and end_date are two date-type fields that are already defined in ACL. Calculating the number of working days between these two dates involves testing for different conditions. For example:

• If the starting date is, for some reason, after the ending date.
• If the starting and ending date both fall on a Saturday or Sunday.
• If either the starting or ending date fall on a Saturday or Sunday.
• If the weekday of the ending date falls before or after the starting date.
• If you need to take into account long weekends or holidays such as Christmas Day, New Year”s Day, and so on.

Here’s how you can do it!

DEFINE FIELD C_Working_Days COMPUTED

This tests to ensure that the starting date is not after the ending date. If, for whatever reason, this condition is true then a value of -999 will be assigned so it can stand out during an audit for further analysis.

-999 IF END_DATE < START_DATE

This tests whether the starting date and the ending date are both on a Saturday or Sunday.

ROUND((END_DATE – START_DATE) / 7.00) * 5 IF (DOW(END_DATE) = 7 OR DOW(END_DATE) = 1) AND (DOW(START_DATE) = 7 OR DOW(START_DATE) = 1)

This tests whether the ending date is a Saturday or Sunday.

INT((END_DATE – START_DATE) / 7.00) * 5 + 7 – DOW(START_DATE) IF DOW(END_DATE) = 7 OR DOW(END_DATE) = 1

This tests if the starting date is a Saturday or Sunday.

INT((END_DATE – START_DATE) / 7.00) * 5 + DOW(END_DATE) – 1 IF DOW(START_DATE) = 7 OR DOW(START_DATE) = 1

This tests whether the weekday of the ending date falls before the weekday of the starting date.

INT((END_DATE – START_DATE) / 7.00) * 5 + DOW(END_DATE) – DOW(START_DATE) + 6 IF DOW(END_DATE) < DOW(START_DATE)

This tests whether the starting date and the ending date are both on a Saturday or Sunday.

INT((END_DATE – START_DATE) / 7.00) * 5 + DOW(END_DATE) – DOW(START_DATE) + 1 IF DOW(END_DATE) > DOW(START_DATE)