Eppplus read incorret date format from excel

Question

0.00/5 (No votes)

See more:

I have a problem with my project ,it is about Human Resources Information System
it has a feature to upload Schedule using excel file ,but in my application
the format of date should be like this

11/30/2019

Month/Date/Year

but in some case user input in wrong format ,
for example like this

30/30/2019

Date/Month/Format

so i need a validation, i give a message for user if the date is invalid format

the message like this

Error Line Description
    Line 2 43783   Incorret Format

i just get stuck and error in condition user input right format but system still validate it as wrong format

What I have tried:

How can i solve this

this is my code

if (DateTime.TryParseExact(schedule_date, "MM-dd-yyyy", new CultureInfo("en-US"), DateTimeStyles.None, out d))                                                    
{count += 0;
 string labelmonth = ddlTest.SelectedValue.ToString()
 string employee_id = worksheet.Cells[i, 1].Value.ToString();
 string user = Session["LogedUserID"].ToString();
 bool validatemonth = ddlTest.SelectedValue.ToString() == 
 DateTime.Parse(worksheet.Cells[i, 3].Value.ToString()).ToString("MM");
 if (validatemonth.Equals(false))
 { count += 1;
  cell1.Text = "Pada Baris ke " + i + "  ";// +employee_id + " " + schedule_date;//" "  
 + Convert.ToDateTime(schedule_date).ToString("MM/dd/yyyy");//+ " Baris ke " + i + "Bulan Tidak Sesuai";
 cell2.Text = "Bulan Tidak Sesuai ";
 row.Cells.Add(cell1);
 row.Cells.Add(cell2);
 mytable.Rows.Add(row);
 }
 }
  else
 {
count += 1;
cell1.Text = "Pada baris Ke " + i + " " + Convert.ToString(schedule_date);//.ToString("MM/dd/yyyy");
cell2.Text = "Format Tanggal Salah";
row.Cells.Add(cell1);
row.Cells.Add(cell2);
mytable.Rows.Add(row);
}
}

Posted 3-Nov-20 22:57pm

Member 14760154

Updated 3-Nov-20 23:23pm

Add a Solution

Comments

PIEBALDconsult 4-Nov-20 10:48am

You should insist on ISO 8601-compliant formats when interchanging data.
And never use strings for dates within an application.

1 solution

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 · 2020-11-03T23:24:00

The problem is that you can't tell: is "01-02-03" the 1st Feb 2003, 2nd Jan 2003, or 3rd Feb, 2001? All are valid: European format, US format, ISO format (kinda - ISO should be four digit year, but there are systems that give less.)

And once you have a date as a string value, there is absolutely no way to tell. Yes, if a date is 01-30-99 then it's obvious, but unless it actually fails basic validation - and less than 2/3 of dates do fail - there is no way to tell what value the user expected.

The only way to do this is to go back to your spreadsheet and look to see if the date info is in a string or (more likely) an Excel date column, and fetch that as a DateTime value directly if it is, rather than converting it to a string when you fetch it.

If it's a string, you are stuffed: you can't tell on an entry-by-entry basis, though if you are lucky you may be able to tell if the values are consistent and fail some validations.