topic How to exclude weekends and holidays with function DateDiff? in ArcGIS Online Questions

How to exclude weekends and holidays with function DateDiff?

RudyJamet — Mon, 30 Oct 2023 14:35:48 GMT

Hello,

I need to create an indicator in Dashboard that displays the number of remaining days between a start date and the end date, that also excludes weekends and holidays in the calculation.

I saw this script

var remaindays = DateDiff($feature.endDate, Today(), 'days');
return when (days>30, "on schedule",
             days>=0, "nearly due",
             "overdue");

but I am struggling to find a way to add the exclusion. I believe that somehow I will need to add +1 or +2 whether there are a Saturday and/or a Sunday in between the 2 dates, and a list of holidays, into the script.

Any help will be greatly appreciated 🙂

Re: How to exclude weekends and holidays with function DateDiff?

jcarlson — Mon, 30 Oct 2023 16:04:43 GMT

Here's a similar post I wrote to remove weekends and holidays from a date span. Not exactly the same problem, but it's an idea.

https://community.esri.com/t5/arcgis-dashboards-questions/arcade-return-a-date-in-the-future-not-including/m-p/1129648/highlight/true#M5592

For your particular question, we're just trying to calculate how many workdays are between today and the end date, minus holidays and weekends. The weekends are easy enough.

// start and end dates var edate = Date(2023, 10, 1) var sdate = Now() Console(sdate, 'to', edate) // get total days between the two var full_days = Ceil(DateDiff(edate, sdate, 'days')) Console(full_days, 'full count of day span') // get partial week gap // positive value means we completed the week and have extra // negative value means we didn't complete the week var partial_gap = Weekday(edate) - Weekday(sdate) Console(partial_gap, 'partial week days') var weeks = Iif( partial_gap >= 0, (full_days - partial_gap) / 7, ((full_days - partial_gap) / 7) - 1 ) Console(weeks, 'weeks') var workdays = full_days - (weeks * 2) Console(workdays, 'workdays') // handling the partial week, looking for other weekends var extra_days = (7 + partial_gap) % 7 // if weekday = 0 or 6, it's a weekend // adding the extra day value to our weekday means 6 == 1 more weekend, 7+ == 2 more workdays = When( Weekday(sdate) + extra_days == 6, workdays - 1, Weekday(sdate) + extra_days > 6, workdays -2, workdays ) return workdays

You can see that other post for ideas about handling holidays, but this should get you a count of non-weekend days between two dates.

Re: How to exclude weekends and holidays with function DateDiff?

DanPatterson — Mon, 30 Oct 2023 16:41:10 GMT

If someone comes across this and needs a python solution.

import numpy as np from datetime import date d1 = date(2023, 1, 1) d2 = date(2023, 12, 31) days = np.busday_count(d1,d2, holidays = ["2023-01-01", "2023-07-01", "2023-12-24"]) print(days)

You can produce a regions specific or user specific "holidays" list

Further info

Dates in NumPy — NumPy v2.0.dev0 Manual

There are other functions that may eventually make it to Arcade over time

Re: How to exclude weekends and holidays with function DateDiff?

RudyJamet — Mon, 30 Oct 2023 17:13:04 GMT

Thank you Josh, I will give it a try.

Rodolphe

Re: How to exclude weekends and holidays with function DateDiff?

RudyJamet — Mon, 30 Oct 2023 17:13:55 GMT

Good to know, thanks Dan.

Rodolphe