Excel date data being read as date type or double type

When within the datatable are represented as

  • as string within one or more formats
  • as double representing an OADate value

then we can handle by a defensive check e.g. for a cleansing in advance or within a LINQ

LINQ Starter help:

(From d In dtData.AsEnumerable
Let dv = d("YourColName").toString.Trim
Let oachk = Double.TryParse(dv, Nothing)
Let dchk = DateTime.TryParseExact(dv,arrFormats,CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing)
Let dp1 = If(oachk, DateTime.FromOADate(CDbl(dv)),New DateTime() )
Let dp2 = If(dchk, DateTime.ParseExact(dv,arrFormats,CultureInfo.InvariantCulture,DateTimeStyles.None), New DateTime())
Let dp = New DateTime(){dp1,dp2}.OrderBy(Function (x) x).Last()
Where dp.Date >= CDate("10/31/2023").Date
Select r = d).CopyToDataTable

Kindly note:

Also we can decopose the LINQ to essential activities