Linq query in excel date

Help Studio
, , ,
1

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

2

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,

image
image776×457 18.7 KB

Hope it helps!!

3

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

dtDates.AsEnumerable().
             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)).
             Max()

e.g. dd.MM.yyyy

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

image
image903×510 28.4 KB

image

4

Hi @purnima_mandan

Can you try the following?

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

image
image979×646 24 KB

Cheers!!

5

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

6

@purnima_mandan

BlankProcess17.zip (45.7 KB)

Note: NewDate variable datatype is System.DateTime

Cheers!!

7

@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:
grafik

Your excel - read range with Workbook
grafik

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

Checking totally invalid dates
grafik

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

8

Hi,

You can use below expression

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

image
image845×517 26.5 KB

FindLatestDate.zip (2.7 KB)

9

this work Thank you @lrtetala

10

@purnima_mandan

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

11

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