EXCEL; For each Activitie with date times

Help
1

Hello Guys,

i got a Problem. I have an Excel sheet filled with date times in two columns. I want to compare a date time that is known from other sources with these two columns. One of the columns have just Start Dates and the other End Dates. I want to compare if a date on a different sheet is between the start and end date.

For example:

image

On a other Excel sheet: 02.05.17

Thanks in advance :slight_smile:

2

Hi,

You can use something like that:
dateToCheck >= startDate and dateToCheck < endDate;

Please take care if you will iterate for each row in your datatable that you extract from your Excel, that you will change the type from string in a DateTime ( ConvertToDateTime(dateString)).

Things to think about though:

DateTime is a somewhat odd type in terms of time zones. It could be UTC, it could be “local”, it could be ambiguous. Make sure you’re comparing apples with apples, as it were.
Consider whether your start and end points should be inclusive or exclusive.

3

@leventbaran

Let us assume that you are having a strabc from other source

Read your Excel sheet and store in a datatable dt
then try this query

dt1= (From p in dt.Select
where DateTime.ParseExact(p.Item(“Start Date Col Ind).ToString.Replace(”.“,”/“),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvarientCulture)<= DateTime.ParseExact(strabc.ToString.Replace(”.“,”/“),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvarientCulture) and DateTime.ParseExact(p.Item(“End Date Col Ind).ToString.Replace(”.”,“/”),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvarientCulture)>= DateTime.ParseExact(strabc.ToString.Replace(“.”,“/”),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvarientCulture)
Select p).ToArray.CopyToDataTable

In place of Start Date Column Index you can Replace with column name and same you can replace with column for End Date Column In

Regards,
Mahesh

1 Like
4

how to extract data from startdate to enddate in excel