How to get next business day with excel support?

Help Studio
1

how to get next business day without excel?
not just for saturday and sunday
but also for holiday in the middle of week

if 10/Jan/2024 Wednesday is holiday
how to skip “10/Jan/2024” as next business day?

I can define the list of holidays in the excel
but machines have different excel environment,
so I’m trying to find different ways.

2

Hi @mountie

How about the following?

image
image1051×698 25.3 KB

currentDate = DateTime.Today

holidayDates = {"01/10/2024"} 

nextBusinessDay = (From day In Enumerable.Range(1, 365)
                  Let tempDate = currentDate.AddDays(day)
                  Where tempDate.DayOfWeek <> DayOfWeek.Saturday AndAlso tempDate.DayOfWeek <> DayOfWeek.Sunday AndAlso Not holidayDates.Contains(tempDate.ToString("MM/dd/yyyy"))
                  Select tempDate).First().ToString("MM/dd/yyyy")

Cheers!!

3

then for more holidays
holidayDates = {“01/10/2024”, “01/13/2024”, “01/14/2024”}

am I correct?

4

@mountie

currentDate As DateTime = DateTime.Now.AddDays(1) ’ Start from tomorrow

Define the list of holidays

holidays As List(Of DateTime) = New List(Of DateTime) From {#1/10/2024#} ’ Add your holidays here

While currentDate.DayOfWeek = DayOfWeek.Saturday OrElse currentDate.DayOfWeek = DayOfWeek.Sunday OrElse holidays.Contains(currentDate)
currentDate = currentDate.AddDays(1)
End While

’ Output the next business day
currentDate

5

similar to the non working days calendar from the triggers we can do:

  • defining a holiday list and store it central within orchestrator
  • using Orchestrator Rest Call to retrieve the holiday list
  • calculating the next business day by filtering out: saturday, sunday and relevant holidays
    • done within the process / xaml implementation
6

Hey @mountie ,
I found and tested solution for you:

  1. First, define a list of holidays.
    VB code 1 :
Dim holidays As New List(Of DateTime) From {
    New DateTime(2024, 1, 10) // Add more holidays as needed
}
  1. Calculate the next business day
    VB code 2:
Dim currentDate As DateTime = DateTime.Today // Or set a specific date
Dim nextBusinessDay As DateTime = currentDate.AddDays(1)
While nextBusinessDay.DayOfWeek = DayOfWeek.Saturday OrElse nextBusinessDay.DayOfWeek = DayOfWeek.Sunday OrElse holidays.Contains(nextBusinessDay)
    nextBusinessDay = nextBusinessDay.AddDays(1)
End While

image
image398×535 40.4 KB

image

7

I found “next business day” definition is more complex than I thought.
it depends on countries, associated companies and associated groups.

so I’m approaching this way

  • calculate number based next business day (just considering saturday and sunday)
  • send confirmation message to the team through corporate messenger
  • correct the day by the confirmed response from the team.