How to compare 2 dates to get the total business days

Help Activities
,
1

Hi,
I am a beginner in UiPath and would like to enquire abt how to compare 2 dates to get the total business days.
Business Days means excluding sat and sun and user-defined holidays from a excel spreadsheet.

I have developed a uipath program for it (pls see attached) but the result (ie. output : BusinessDays) seems like not correct

My uipath sw version is 2019.10.1 Enterprise Edition

Appreciate your help.
Many thanks.

CompareTwoDatesFromExcel.zip (48.6 KB)

BR,
Raymond

2

Good afternoon @pzgqq414!

I’ve looked at your solution, and I’ve added a few tweaks in order to make it work without custom activities (attaching it at the end, explaining the solution below):

1st change: I created a 2nd sheet on your Data.xlsx file, with the list of holidays days, to simulate that entry.
2nd change: By using LINQ, I’ve excluded from the range of days between dataEntry1 and dataEntry2 the days which are “Saturday” or “Sunday”, as well as any day included in the list of holidays.

LINQ Explanation:
1st Assign: Get list of holidays

dt_Holidays.AsEnumerable.Select(function(x) DateTime.ParseExact(x("Holidays").toString, "MM/dd/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)).ToArray
  • This LINQ converts all the items on the Holidays Tab, into an array, so that we can exclude them from the matches later on.

2nd Assign: Get days based on rules

Enumerable.range(0, (dataEntry2-dataEntry1).Days).Where(
Function(x) 	(Not (dataEntry2.AddDays(x).DayOfWeek).toString.toLower.Equals("saturday") AndAlso 
					Not (dataEntry2.AddDays(x).DayOfWeek).toString.ToLower.Equals("sunday")) AndAlso
					Not var_arr_Holidays.Contains(dataEntry1.AddDays(x))
).ToArray

We get a list of days by checking the number of days between End Date, and Start Date. and we build a range {0,1,2,3,4,5…}. With LINQ filter (.Where) we filter them out by removing the ones that end up on “Saturday” or “Sunday”.

  • That is done by taking the init date, and we keep on adding the values from the range (0,1,2…) until we reach the last day.
    the 3rd condition is that if the day is inside the array of holidays days, we also filter it out.

At the end, by counting the numbers that were left after applying the LINQ (array_numbers.Count.toString), you’ll get the number of days that are not weekend, and also not in the list of holidays.

Hope it helps!

Best Regards,
Ignasi

Dates_RemoveHolidaysandWeekends.zip (46.2 KB)

3

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.