Order aging calc that subtracts weekends

[jkrause]

I need to calculate the age of an open order but not include the weekend. Anyone willing to share how they do this? The calculation currently subtracts the order added date from the sysdate. Though rare, an order can be open over two weekends. Appreciate any insight anyone has. Thanks!

[etac]

Hi Jill,

Great question!

I wrote out a detailed explanation for this one in another post that you can read through to get the detailed explanation

Subtracting Weekends from Date Range Calculations

To subtract weekends from date range calculations we’ll need to break the explanation in different chunks. 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) Then find the number of full days between the 2 dates themselves 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 lo…

Otherwise, you can use the following snippets directly to get the calculations sorted out:

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))

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))

Let me know if you have any further questions on this.

[jkrause]

Thank you Edip!