@K_IKT031595
Please follow the suggested flow.
We utilize an Excel sheet to store holiday data. This data is then converted into a list and passed as an input parameter to the .NET script. The script processes the input date and performs necessary calculations to determine the last weekday prior to the entered date.
.Net scripts do the math and it will return the last week day of entered date by excluding week dates holidays list.
Code:
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Documents\\Holidays.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Display.InputDialog Title: $'''Enter enter the date to check''' Message: $'''Enter enter the date to check in the format of (YYYY/MM/DD)?''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> EnteredDate ButtonPressed=> ButtonPressed2
SET Out_Date TO $'''%''%'''
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: 0 ColumnAsList=> HolidayAsList
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $'''
// Step 1: Calculate Yesterday\'s Date
DateTime yesterday = Convert.ToDateTime(In_Date).AddDays(-1);
// Step 2: Check if Yesterday is a Weekday
while (yesterday.DayOfWeek == DayOfWeek.Saturday || yesterday.DayOfWeek == DayOfWeek.Sunday)
{
yesterday = yesterday.AddDays(-1); // Move to previous day
}
// Step 3: Check if Yesterday is a Holiday
while (holidays.Contains(yesterday))
{
yesterday = yesterday.AddDays(-1); // Move to previous day
}
// Step 4: Output the Most Recent Weekday Date on or Before Yesterday
Out_Date= yesterday.ToString(\"yyyy-MM-dd\");''' @'name:holidays': HolidayAsList @'type:holidays': $'''List''' @'direction:holidays': $'''In''' @'name:In_Date': EnteredDate @'type:In_Date': $'''String''' @'direction:In_Date': $'''In''' @'name:Out_Date': $'''''' @'type:Out_Date': $'''String''' @'direction:Out_Date': $'''Out''' @Out_Date=> Out_Date
Display.ShowMessageDialog.ShowMessage Title: $'''Last week day''' Message: $'''Last week day is : %Out_Date%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
