Subtracting Weekends from Date Range Calculations

Tips & Tricks
1

To subtract weekends from date range calculations we’ll need to break the explanation in different chunks.

  1. We need to find the amount of full weeks between the dates you’re comparing – in this case between the sysdate(0) and the starting date (ORDER_ADDED_DATE)
  2. Then find the number of full days between the 2 dates themselves
  3. Then based on the starting day of week, ending day of week, and the number of weeks between the dates calculate the number of weekend dates

If you want to just use the logic to the calculation directly:
please find the snippet below for how to calculate number of weekends:
add(multiply(floor(divide(add(floor(divide(subtract(sysdate(0),ORDER_ADDED_DATE),86400000)),1),7)),2),condition(greaterThan(add(absolute(subtract(dayOfWeek(sysdate(0)),dayOfWeek(ORDER_ADDED_DATE))),dayOfWeek(ORDER_ADDED_DATE)),7),1,0),condition(equals(dayOfWeek(ORDER_ADDED_DATE),1),1,0))

The below snippet will directly exclude weekends between sysdate and ORDER_ADDED_DATE
subtract(subtract(sysdate(0),ORDER_ADDED_DATE),multiply(add(multiply(floor(divide(add(floor(divide(subtract(sysdate(0),ORDER_ADDED_DATE),86400000)),1),7)),2),condition(greaterThan(add(absolute(subtract(dayOfWeek(sysdate(0)),dayOfWeek(ORDER_ADDED_DATE))),dayOfWeek(ORDER_ADDED_DATE)),7),1,0),condition(equals(dayOfWeek(ORDER_ADDED_DATE),1),1,0))
,86400000))

You can then convert the above to hours, days, etc. by doing a division as per usual.

The details of each of the steps above we end up with this:

To get the full weeks between the dates we need the following

  1. Full weeks between the dates
    a. Get the number of days between the starting date and ending date
    i. add(floor(divide(subtract(sysdate(0),ORDER_ADDED_DATE),86400000)),1)
    1. difference between the dates in DAYS would be done by converting the difference to days – 10006060*24 = 86400000
    2. take the floor of the 2 days to get the full number of days
    3. add 1 to that since at the very least if you’re on the same day, you’re still within 1 day difference between the 2 dates
    b. Then take the value from above – lets call it “DAYS_BETWEEN” and get the number of full weeks between the 2 dates
    i. floor(divide(DAYS_BETWEEN,7))

Then, to get the weekends between the dates

  1. Get the day of week (DOW) for the dates – NOTE – Saturday = 7, Sunday = 1
    a. Get the day of week for the starting date (ORDER_ADDED_DATE) and the ending date (sysdate(0))
    i. STARTING_DOW = dayOfWeek(ORDER_ADDED_DATE)
    ii. ENDING_DOW = dayOfWeek(sysdate(0))
  2. Get the remaining full days based on days of week – lets call it REMAINING_WEEK_DAYS
    a. add(absolute(subtract(ENDING_DOW,STARTING_DOW)),STARTING_DOW)
    i. this logic is basically bringing in days between the ending and starting days of week
    ii. this allows us to determine based on starting and ending days of week how many week days/weekend we can calculate the date ranges

With the above, we can then generate the number of weekends between the dates
3) Combining the above, we can then get the number of weekends between the dates in total
a. add(multiply(WEEKS_BETWEEN,2),condition(greaterThan(REMAINING_WEEK_DAYS,7),1,0),condition(equals(STARTING_DOW,1),1,0))
i. multiply(WEEKS_BETWEEN,2)
1. each full week has 2 weekend days so multiply it by 2
ii. condition(greaterThan(REMAINING_WEEK_DAYS,7),1,0)
1. if in addition to the full weeks, we are starting at Friday, Saturday, or Sunday, add 1 day to account for starting from the a day that falls into a weekend date
iii. condition(equals(STARTING_DOW,1),1,0)
1. If you’re starting on a Sunday, then add 1 day to account for the weekend

Note on weekend days calculation above:

There is some complexity as to why we’re doing that general logic but the breakdown is as follows:

  1. If the REMAINING_WEEK_DAYS between the 2 dates is greater than 7, then the dates will overlap through a weekend and will have 2 weekend dates
  2. If the REMAINING_WEEK_DAYS between the 2 dates is exactly 7, then you have 1 weekend day between Mon-Sat or 2 weekend days if its occurring on Sunday
  3. If the REMAINING_WEEK_DAYS between the 2 dates is less than 7, then you have no weekend days between Mon-Sat or have 1 weekend day if its occurring on Sunday