The following set of actions could do the trick:
Here's a snippet that you can copy and paste directly into PAD to create these actions automatically:
DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
SET StartDate TO $'''%''%'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\RPA\\DatesList.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: $'''Date''' ColumnAsList=> DateList
Variables.SortList.SortList List: DateList
LOOP LoopIndex FROM DateList.Count - 1 TO 0 STEP -1
IF CurrentDateTime > DateList[LoopIndex] THEN
SET StartDate TO DateList[LoopIndex]
EXIT LOOP
END
END
NOTES
Input file
I just created a very simple Excel file with a single column called 'Date' and the dates there:
That's why I use 'Date' in the Retrieve data table column into list action to get the dates into a list. If your column will be named anything else, you might need to change that in the action.
Retrieving the column
The Retrieve data table column into list action is needed because Read from Excel worksheet will return a table by default, but you need a list to be able to use the Sort list action.
Sorting the list
The Sort list action sorts ascending by default and you cannot change that, unless you enable sorting by item properties. Since I assume the text in Excel to be strings, those do not have valid properties to sort by, so I kept the default and then in the Loop action, I made the flow go backwards from the last item to the first one. This way I start with the latest date and use Exit loop as soon as I find the first date that is lower than the current date.
Date format
The dates in my Excel file are actually formatted in a way that Excel recognizes as date values. This results in PAD also recognizing the values as dates. So, I do not need to convert them to dates and can in fact compare them with the current date right away. However, if your dates are interpreted as strings, you might need to use the Convert text to date time action inside the Loop before using the condition, so that you have two dates to compare, instead of comparing a date to a string (which would result in a type error).
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.
If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/
