Find nearest to a date

Help Studio
1

I have string variable like “20250122” And a data table which has multiple rows. One of the column have dates eg “2025-02-12”.. I need to find the row whose date is nearest to the variable…

1 Like
2

Hi @pandeypriyanka1312

Try below -

nearestRow = dt.AsEnumerable().
OrderBy(Function(r) Math.Abs(CDate(r(“DateColumn”)) - CDate(dateString.Insert(4, “-”).Insert(7, “-”)))).
FirstOrDefault()

For example, if dateString = “20250122” and dt has dates like “2025-01-20” and “2025-01-25”, the nearest row will be “2025-01-20”.

Found helpful pls mark as a solution. Thanks

1 Like
3

@pandeypriyanka1312

just to add on to the solution. Do make sure to handle cases of more than one row being closer. Since there is no Time (hh:mm:ss), you might have cases for example:
input Date = “20250122”
row1 = “2025-01-23” and row2 = “2025-01-21”. Both are closer by 1 day.

1 Like
4

Thanks @V_Roboto_V for highlighting this scenario.. There is time column available in table. Can you suggest solution?

5

Thanks @V_Roboto_V

For that make sure which one you want to consider -

Option 1: Pick the Earliest Date

closestRow = dt.AsEnumerable().
Where(Function(row) Math.Abs((DateTime.Parse(row(“Date”).ToString()) - inputDate).Days) = minDiff).
OrderBy(Function(row) DateTime.Parse(row(“Date”).ToString())).
First()

Option 2: Pick the Latest Date

closestRow = dt.AsEnumerable().
Where(Function(row) Math.Abs((DateTime.Parse(row(“Date”).ToString()) - inputDate).Days) = minDiff).
OrderByDescending(Function(row) DateTime.Parse(row(“Date”).ToString())).
First()

Use accordingly @pandeypriyanka1312

1 Like
6

If there is a Time column available in the table, could you show me a sample format? (Is it in 12 Hour or 24 Hour, etc…)
Even then, the probability of conflicting rows will only reduce if you have seconds as well. For example, “2025-01-21 12:00:00” and “2025-01-22 12:00:00” are both closer to Input Date “2025-01-22 00:00:00”. :joy: So, in the end, you must implement some sort of Exception Handling mechanism to tackle these scenarios based on Business user Requirements.

Here, is a small tweak to the previous solution:
Assumption: Time column is in 24 Hour format

closestRow = dt_Input.AsEnumerable().OrderBy(Function(row) Math.Abs(CType(DateTime.ParseExact(row("Date").ToString() + " " + row("Time").ToString(), "yyyy-MM-dd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture), DateTime).Subtract(DateTime.ParseExact(inputDate + " 00:00:00", "yyyyMMdd HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)).TotalDays)).FirstOrDefault()

The Sample Flow:

The image shows a workflow sequence in UiPath, including activities to build a data table, assign a row, and log a message, with variable definitions displayed at the bottom. (Captioned by AI)
The image shows a workflow sequence in UiPath, including activities to build a data table, assign a row, and log a message, with variable definitions displayed at the bottom. (Captioned by AI)1142×684 29.6 KB

The Sample Datatable:

A data table with columns for Date and Time is visible, containing entries from January and February of 2025. (Captioned by AI)
A data table with columns for Date and Time is visible, containing entries from January and February of 2025. (Captioned by AI)624×430 16.8 KB

The Output:

This image shows the Immediate window of a debugging session displaying a DataTable of date-time values and the closest matched DataRow entry. (Captioned by AI)
This image shows the Immediate window of a debugging session displaying a DataTable of date-time values and the closest matched DataRow entry. (Captioned by AI)555×335 6.46 KB

Note: If the Time is in 12 Hour AM/PM format, just replace the “yyyy-MM-dd HH:mm:ss” with “yyyy-MM-dd hh:mm:ss tt”.

If this solves your issue, Do mark it as a solution.
Happy Automation :star_struck:

1 Like