compare two dates in string format while reading from excel

Question

0.00/5 (No votes)

See more:

Hi,

I have two date fields in excel field. I want to compare these two fields. But since the value of excel fields is string convert them into DateTime may cause problem. So is there any way to compare them? I think its not possible, even if it is it won't be 100 % fail safe. Its my hunch nothing much. Please let me know if there is a way

Posted 3-Apr-14 0:19am

Arjun Menon U.K

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-04-03T00:39:00

Solution 1

No, it's not really possible, and it certainly isn't "safe" - you really do want to convert them to a DateTime value first and then do the comparison.

It's all ok to do a string compare if the dates are in ISO format: yyyy-MM-dd
Because string comparisons stop at the first difference and use character comparisons to determine order. But...if one date is 2014-01-3 and the other is 2014-01-14 then a string comparison gets them in the wrong order.

You could manually process the string and do the comparison on the various parts - but then you are duplicating the work that converting it to a DateTime does anyway! :laugh:

Posted 3-Apr-14 0:39am

OriginalGriff

Comments

Arjun Menon U.K 3-Apr-14 6:45am

Hi ,

I can assure you that both string value will be in same format. But what i find nearly impossible is to find the format of the data. like its dd/mm/yyyy format or mm/dd/yyyy format. Culture info do play a role rt?

OriginalGriff 3-Apr-14 6:59am

Yes - you could look at the current culture and work it out from that...but was the Excel file produced on that PC? Because you can't tell from just looking is 01-02-2014 is Jan or Feb...
If they are all the same, are you sure they aren't formatted in the Excel file? Personally, I always use CTRL+; in Excel to insert today, which inserts a date format value.
How are you reading the file? OleDB?

Arjun Menon U.K 3-Apr-14 7:08am

Hi,
That's the problem bro. If i can't know the format of date how on earth i can compare two dates. Like you said 01-02-2014 can be either Feb 1st 2014 or Jan 2nd 2014. How can i get the current culture?It's merely a string value. The data in excel is exported from TFS server, a work item like task,bug,user story anything like that

OriginalGriff 3-Apr-14 7:16am

Depends - I assume that you are using OleDB to access the file? If so, I know of no way to get the culture directly (you can get it - I think, I haven't tried - with Excel Interop apparently:
http://www.breezetree.com/blog/index.php/common-mistakes-programming-excel-with-c-sharp/
Let me know if it works!)
The other solution is to assume "month first" and look at all rows to see if they work. If they don't then try "day first" and check that. Nasty - but there is no real solution if you can't tell the culture that the file was written in.

Arjun Menon U.K 3-Apr-14 8:34am

mmm lemme check. Thanks anyways

Arjun Menon U.K 3-Apr-14 9:04am

Hi, when i read values using this code, the date got converted to a double value. why is that so?
xlRange.Cells[2, colIndex].Value2.ToString()

But when i use value instead of value2 it works fine . What's the difference?

OriginalGriff 3-Apr-14 9:45am

:HUGE GRIN SMILEY:
That, my friend, could well save your day...if you use DateTime.FromOADate which takes a double value, it returns it as a DateTime - from an OleAutomation source, of which Excel is one. Which would mean that Excel knows it's a date and will happily provide it as such...neatly solving your problem!

Arjun Menon U.K 3-Apr-14 9:51am

how it will save my day ????? ;) . because excel will contain many types of column values na?
Here's the code
private void ReadExcel()
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(openFileDialog1.FileName);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;

DataTable dtResult = new DataTable();

//Create columns for dtResult
for (int colIndex = 1; colIndex < colCount; colIndex++)
{
dtResult.Columns.Add(xlRange.Cells[2, colIndex].Value2.ToString());
}

for (int i = 3; i <= rowCount; i++)
{
DataRow _dr = dtResult.NewRow();
for (int j = 1; j <= dtResult.Columns.Count; j++)
{
_dr[dtResult.Columns[j - 1]] = xlRange.Cells[i, j].Value != null ? xlRange.Cells[i, j].Value.ToString() : "";
}
dtResult.Rows.Add(_dr);
dtResult.AcceptChanges();
}
dataGridView1.DataSource = dtResult;
}

OriginalGriff 3-Apr-14 9:58am

Yes - but...the Value2 property returns the underlying datatype of the field, so there is a very good chance that it's giving you the date in a ready-to-go form that doesn't have to be parsed, just converted to a DateTime

Arjun Menon U.K 3-Apr-14 10:01am

Dude, but there is an issue. When i select an excel file. it's shown in grid. so if i use value2 it will show that double value, which will cause problem and when i click another button, then its read from this datagrid and saved. So,while reading from this datagrid,since its double value it will cause problem right?

OriginalGriff 3-Apr-14 10:10am

So just use it as a datetime:
dtResult.Columns.Add(DateTime.FromOADate(xlRange.Cells[2, colIndex].Value2));
You can convert it to a string if you want...but I'd rather leave it as a DateTime if I could.

Arjun Menon U.K 3-Apr-14 10:12am

before doing that conversion i must check its of date format rt
because other columns can have int,float,string values

OriginalGriff 3-Apr-14 10:14am

Well yes - but I assumed you had abstracted that code: I would be setting the appropriate type into the table anyway, rather than just strings.

Arjun Menon U.K 3-Apr-14 10:16am

you mean set data type in excel? well i can't do that. i can't do a thing on excel. otherwise i would have set the date in a universal format :P.
If you meant it in the datatable, how can i set the column data type as i am just reading the excel values.

OriginalGriff 3-Apr-14 10:28am

No! :laugh: Set it in your DataTable, so an integer in your Excel file is an integer in your table: you know what the various columns should hold, or you wouldn't be reading the file!

That way, whatever manipulations you are going to do later are working with native datatypes and will not need any manipulation - which gives your "higher up" code a better degree of data independence from the Excel source.

Arjun Menon U.K 3-Apr-14 10:32am

One thing i must say at first say is that excel templates change. Column names are dynamically set as per my above code.
I think am back to square one . Sorry .. The column values say ID has value 11, then as you have said i should set the datacolumn datatype as int rt? This is the reason why i wrote like this
for (int colIndex = 1; colIndex < colCount; colIndex++)
{
dtResult.Columns.Add(xlRange.Cells[2, colIndex].Value2.ToString());
}

So i must get the value of each column so that i can set the datatype right?

BELGIUMsky · Answer 2 · 2014-04-03T00:41:00

If you have Data format on the fields you want to compare.
You can know for sure the field will be in a datetime format.

for more info on data format of fields/rows/columns: format-a-date in excel[^]

then you could do DateTime.Parse(your field).

When you have two instances of DateTime structure you can use operators "==", "!=" and ">", "<", ">=", "<=" in the sense "later", "earlier", "not earlier", "not later", respectively. Also, you can use subtraction operator ("-") which returns the result in the form of System.TimeSpan. This is a comprehensive set of operators to cover all your comparison needs.