Calculating Working Day vs Calendar Day

[mgreenberg]

Hi - We are try to create logic that will determine whether a scheduled job would run. We have been able to create the logic to only run if current date is <= to the day of the month (Calendar Day) but what we really want is to be able to know if it is before for a specific working day so excluding weekends (for example working day 10 in September 2021 would be the 14th) . Has anyone created this logic and would be willing to share?

[ckattookaran · Accepted solution]

Here is something you can do. I did add in a Holiday Table as well, where you can add the holiday dates.

Call the function in the following way.

Dim holidayDT As DataTable
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim sql As New Text.StringBuilder
sql.AppendLine(“SELECT HOLIDAY_DT”)
sql.AppendLine(“FROM XFC_HOLIDAY”)
holidayDT = BRApi.Database.ExecuteSql(dbConn, sql.ToString, True)
End Using

BRApi.ErrorLog.LogMessage(si, Me.GetWorkingDay(si, Date.Now, holidayDT))

The function which gets the Working day.

Private Function GetWorkingDay(ByVal si As SessionInfo, ByVal currentDate As Date, ByVal holidayTable As DataTable) As Integer
Try
Dim workingDate As Date = currentDate ’ Create a new date since we are going to modify the start date
’ Get the first day of the start date
Dim firstDateOfMonth As Date = New DateTime(currentDate.Year, currentDate.Month, 1)
’ Get the current day
Dim currentDay As Integer = currentDate.Day
’ Get total days from first day to the current day
Dim totalDays As Integer = DateDiff(DateInterval.Day, firstDateOfMonth, workingDate) + 1

  Dim offDays As Integer = 0
  ' We are checking whether days from first day till current day is a holiday
  ' We are also checking whether it is a weekend
  For i As Integer = currentDay To 1 Step -1
  	If Not (workingDate.DayOfWeek < DayOfWeek.Saturday And workingDate.DayOfWeek > DayOfWeek.Sunday And holidayTable.Select("HOLIDAY_DT='" & workingDate.ToString("MM/dd/yyyy") & "'").Count = 0) Then
  		offDays += 1
  	End If
  	' set the date back as we are starting from current date
  	workingDate = workingDate.AddDays(-1)
  Next

  Return totalDays-offDays

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

I checked this for today, and I got the working day as the 13th (since the 6th was a holiday).

[ckattookaran]

You can set a parameter to indicate which working day it should run, Use an extender rule to check whether the parameter and the function’s return above are equal, then trigger the DM sequence. If it is not, don’t do anything. However, the schedule should run every weekday to check whether you should run it or not.
It is an excellent content for a blog post, so thank you for asking this.

[mgreenberg]

Thank you! This works perfectly. I have very happy users now!