How to exclude weekends and holidays with function DateDiff?

RudyJamet

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 🙂

jcarlson

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.