Linq to compare Dates from Excel Table

Hi,

I have an excel file which which have dates in Column “Effective Date”(Sometimes the Date formate is coming as “12/23/2020” and sometimes it comes as 1/2/2020 and sometimes it comes as 2/31/2020), and another Roaster Date(MM/dd/yyyy) is string variable, what i need is to compare all Effective Dates with Roaster Date, and if it Effective Date is on or Before Roaster date, that row should be added to another Sheet. I can do it using Data Table , For Each Rows, .Net Methods, but i want the code using Linq.

TIA,
Santhosh
Book1.xlsx (8.1 KB)

@Santhosh_Hyd
find some starter help here:

Date Filtering:
DT_FilterDates_Yesterday.xaml (8.3 KB)

Handling multiple date formats:
FilterDates_NonValidDateFormat.xaml (8.0 KB)

Just combine the essential building blocks in your implementation and it should work

1 Like

Hi, Thanks for the reply , i kinda tried this, but not much helpful, can you please post the linq query which would be sufficient for my requirment?

@Santhosh_Hyd
from the different provided XAMLs following was taken:

Defining the different formats + the general pattern used in case of Excel is detecting cell format as date

(From d In dtData.AsEnumerable
Where now.AddDays(-1).Date = dateTime.ParseExact(d(0).toString,"dd/MM/yyyy HH:mm:ss", CultureInfo.InvariantCulture).Date
Select d).toList

Changed to:

(From d In dtData.AsEnumerable
Let dtp = DateTime.ParseExact(d("Effective Date").toString,Formats, CultureInfo.InvariantCulture, DateTimeStyles.None)
Let ctp = DateTime.ParseExact(strCheckDate,"MM/dd/yyyy", CultureInfo.InvariantCulture)
Where dtp.Date <= ctp.Date
Select d).toList

As you see, the essential parts were already there and required just a little modification

find starter help here:
Santhosh_Hyd.xaml (7.9 KB)

2 Likes

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