Performant working day calculation

Modified on Thu, 12 May, 2022 at 10:41 AM

Omniscope Evo has the NETWORKDAYS formula function which lets you calculate weekdays (Mon-Fri) between two arbitrary dates. However this function does not have awareness of extra public holidays in a given year.

The attached workflow lets you solve this problem with good performance. 

To use this:

  • You will need some data containing Start and End date fields, for which you want to calculate working days between them (inclusive).

  • Import the attached "Working days.IOZ" file (shown above).

  • Configure the date range in the "All data" block according to the earliest and latest dates in your data. You can go wider, but don't go crazy; it works using all possible date range combinations, so will be slower to execute if the range is too wide. Use 00:00 for time.

  • Provide a list of public holidays for the same period - edit the "All public holidays" block accordingly. Feel free to change the structure of the data and workflow as long as the input data structure to the "Add count 1" block is unchanged.

  • Pass your own data instead of the "Example dataset" block.

  • Configure the "Join with data" block to ensure the correct fields are chosen for Start and End date. 
  • Execute. A new field will be added, "Workdays between".

Alternatively, you can write the "Final lookup data" block data to an IOD file using a File output block. 

You can then reuse this lookup file along with a Join block to perform the working day calculations without the overhead of producing the lookup data, providing the dates fall within the period that was configured when building the lookup file.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article