Compare Time column in two different excels and get matching data

Help Activities
, ,
1

Hi,

I want to compare two excels with Time column and get matching data. Excel1 data in 12hr format and Excel2 data in 24hr format. compare these two data columns with hour and minutes.

Could you please help me on this.

Sample data:

Excel1
Time
7:03:34 AM
7:03:34 AM
7:03:34 AM
7:03:34 AM
7:03:34 AM
7:03:34 AM
7:03:34 AM
7:03:34 AM

Excel 2:
Time
10:40:09
7:34:19
13:17:34
13:10:08
12:16:18

Convert any excel data to required format and exclude seconds to compare with hours and minutes.
Note: Its bulk data we cant use for each row.

2

@Manaswini_UI

Timespan.parse(value) can be used to parse the date time values…read the data into datatable then you can know the format as well if you want to specify a format

Once you have the parse time…we can get minutes hours seconds everything separately and check

Cheers

3

Covert the excel1 time format to 24hr format this way

String Excel1Time =DateTime.ParseExact(Excel1_Time.ToString(),“h:mm tt”, System.Globalization.CultureInfo.InvariantCulture).ToString(“hh:mm”)

And then compare it with the Excel2Time.ToString(“hh:mm”)
Hope this helps

4

can you give linq query for this. we need to exclude seconds

5

Here’s the Query

dt_fitered = dt_Excel1.AsEnumerable.Where(Function(row,Index) DateTime.ParseExact(row(“Time”).ToString(),“h:mm tt”, System.Globalization.CultureInfo.InvariantCulture).ToString(“hh:mm”).Equals(dt_Excel2.Rows(Index)(“Time”).ToString(hh:mm)).CopyToDataTable

Check this Out!
Hope this helps!

2 Likes
6

This part will remove the seconds .ToString(“hh:mm”)

7

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