Linq query in excel date

so i have an excel in that there is column name is date so in that there are so many date given so i want to find latest date using linq query my excel data sheet are attached
input.xlsx (8.5 KB)
so i want output is 08-01-2024

Hi @purnima_mandan

→ Use the read range workbook activity to read the excel and store it in a datatable datatype variable called dt.
You can use the below Linq expression in assign activity.

- Assign -> latestDate = dt.AsEnumerable().Max(Function(row) If(row("Date") IsNot DBNull.Value AndAlso Double.TryParse(row("Date").ToString(), Nothing), DateTime.FromOADate(CDbl(row("Date"))), DateTime.MinValue)).ToString("dd/MM/yyyy")

Note - latestDate is the String datatype variable.

Check the below workflow for better understanding,

Hope it helps!!

Hey @purnima_mandan
Depends of date format try this:
e.g. MM/dd/yyyy

             Where(Function(row) Not String.IsNullOrWhiteSpace(row("Date").ToString())).
             Select(Function(row) DateTime.ParseExact(row("Date").ToString(), "MM/dd/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)).

e.g. dd.MM.yyyy

             Where(Function(row) Not String.IsNullOrWhiteSpace(row("Date").ToString())).
             Select(Function(row) DateTime.ParseExact(row("Date").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture)).


Hi @purnima_mandan

Can you try the following?

DT.AsEnumerable().Max(Function(row) DateTime.FromOADate(CDbl(row("Date"))))


i will get error in all of these solution like error is String ‘45285’ was not recognized as a valid DateTime. can you share xmal file with output because i dont get output from above solution

@purnima_mandan (45.7 KB)

Note: NewDate variable datatype is System.DateTime


when working with dates and Excel we check the datatable contents and will not rely at the visual presented format from excel

Your Excel- read range with EAS:

Your excel - read range with Workbook

To find the latest date within a defensive approach we can do the following and run it against a prepared test set:

Checking totally invalid dates

Assign Activity:
MaxDate | DateTime =

(From d In dtData.AsEnumerable
Where Not (isNothing(d(0)) OrElse String.IsNullOrEmpty(d(0).toString.Trim))
Let ds = d(0).ToString.Trim
Where System.Text.RegularExpressions.Regex.IsMatch(ds,"^\[d.]+$|^[\d.-\/]{10}$")
Let sd = DateTime.MinValue
Let dp1 = If(Double.TryParse(ds,Nothing), DateTime.FromOADate(CDbl(ds)),sd)
Let dp2 = If(dp1 = DateTime.MinValue, DateTime.ParseExact(ds, "dd.MM.yyyy", Nothing), dp1)
Order By dp2 Descending
Select r=dp2).FirstOrDefault

we initial did setup a parsing cascade and can extend it e.g. handling dd.MM.yyyy and MM/dd/yyyy etc as long it is not ambigous

Whenever a new level from the cascade is not fullfilled we fall back to the previous result

For sure we can extend, adapt and also include the toString(“dd-MM-yyyy”) into the LINQ


You can use below expression

LatestDate = Dt.AsEnumerable() _
.Max(Function(row) DateTime.FromOADate(row.Field(Of Double)(“Date”)).ToString(“dd/MM/yyyy”)) (2.7 KB)

this work Thank you @lrtetala


Glad to be helped…If no more questions can you please close the topic

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