Linq query in excel date

[ppr (Peter Preuss)]

@purnima_mandan
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