cancel
Showing results for 
Search instead for 
Did you mean: 

Time difference calculation excluding weekends

ahouston
New Contributor II

Does anyone have a pattern available to determine difference between two timestamps and add the duration to another timestamp, both of which calculations would exclude weekends?

For example, three inputs and one output:

  1. Baseline start timestamp
  2. Baseline end timestamp
  3. Target start timestamp

Logic would find the time diff between 1 and 2, excluding weekends and add that time to 3, excluding weekends to land on target end timestamp.

For example, using reference range of last Friday 1/28 until Thursday 2/3 and target start of Friday 2/4. The 4 day difference would be calculated and added to 2/4 - excluding the coming weekend, result would be Thursday 2/10.

Thanks!
Adam

1 ACCEPTED SOLUTION

bojanvelevski
Valued Contributor

We can add some specific number of days to this calculation, which is basically a failsafe mechanism for picking up working days. Before it was just multiplying the difference, just to be sure, but adding, let’s say 4 days will help the logic to cover this edge cases.

image

Or we can complicate the second expression by adding more logic to it, again regarding the exclusion of the non working days.

I did some unit testing and it seems fine, but you can try more scenarios with both fixes and let me know if we need more updates.

Dates Difference Updated_2022_02_03.slp (8.1 KB)

View solution in original post

5 REPLIES 5

bojanvelevski
Valued Contributor

Hey @ahouston,

I believe that the pipeline below will give you the result you need:

Dates Difference_2022_02_03.slp (5.1 KB)

It can be done in one mapper, but it’ll become a bit messy.

Bojan

ahouston
New Contributor II

This is fantastic Bojan! Very much appreciated.

ahouston
New Contributor II

I really like the approach taken although this doesn’t work when difference is one or two days and target start is a Friday. For example:
{
“startDate”: “2022-01-28”,
“endDate”: “2022-01-31”, <<2022-02-01>>
“targetStartDate”: “2022-02-04”
}

In these cases, the filter range and and and index get out of alignment.

Is there an elegant way to solve? I’m building ternary operations for each exception but thinking there has to be something cleaner.

bojanvelevski
Valued Contributor

We can add some specific number of days to this calculation, which is basically a failsafe mechanism for picking up working days. Before it was just multiplying the difference, just to be sure, but adding, let’s say 4 days will help the logic to cover this edge cases.

image

Or we can complicate the second expression by adding more logic to it, again regarding the exclusion of the non working days.

I did some unit testing and it seems fine, but you can try more scenarios with both fixes and let me know if we need more updates.

Dates Difference Updated_2022_02_03.slp (8.1 KB)