custom date in the excel

Question

Hi all, 

 

my custom date format is Excel cell contains date value in the format Mon-YYYY.

example = excel cell contain apr-14 but actual cell value is 28-04-2014 and second cell value apr-14 but value is 13-04-2014.

but i used function following ways: 

public static DateTime? CleanDateField(string DateField)

{

//double dbl = Convert.ToDouble(DateField);

//string strDate = DateTime.FromOADate(dbl).ToShortDateString();

// Convert the text to DateTime and return the value or null

DateTime? CleanDate = new DateTime();

int intDate;

bool DateIsInt = int.TryParse(DateField, out intDate);

if (DateIsInt)

{

// If this is a serial date, convert it

CleanDate = DateTime.FromOADate(Convert.ToDouble(DateField));

}

else if (DateField.Length != 0 && DateField != "1/1/0001 12:00:00 AM" &&

DateField != "1/1/1753 12:00:00 AM")

{

// Convert from a General format

CleanDate = (Convert.ToDateTime(DateField));

CleanDate = DateTime.Parse(DateField);

}

else

{

// Date is blank

CleanDate = null;

}

return CleanDate;

}

but i am getting cell values for apr-14 is 01-04-2014 which is wrong i have to retrieve date
values while importing
1) first cell value is 28-04-2014
2) second cell value is 13-04-2014

Plz help i search on google i did not find correct solution
thanks in advance

 

My Code is following:

btnimport clicked button:

 

string strTempPath = System.Configuration.ConfigurationManager.AppSettings["TempPath"].ToString();

string strFileName = fluplEarningsFile.FileName.ToString();

string strMainFilename = strTempPath + strFileName;

string oledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strMainFilename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

fluplEarningsFile.SaveAs(strTempPath + strFileName);

if (fluplEarningsFile.HasFile)

{

DataSet ds;

DataSet dssubsector;

OleDbDataAdapter dataadapter;

SqlConnection con = new SqlConnection(connString);

OleDbConnection OLEConn = new OleDbConnection(oledbCon);

OleDbCommand oledbComm = new OleDbCommand();

string strQuarterMonth;

string strQuarterYear;

oledbComm.Connection = OLEConn;

OLEConn.Open();

dataadapter = new System.Data.OleDb.OleDbDataAdapter(oledbComm);

string strSelect = "select * from [Earnings$]";

oledbComm.CommandText = strSelect;

ds = new System.Data.DataSet();

dataadapter.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)

{

for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)

{

string strQuarter = ds.Tables[0].Rows[i][2].ToString();

if (strQuarter != "\n " && strQuarter != "")

{

string[] strSplitQuarter = strQuarter.Split('/');

strQuarterMonth = strSplitQuarter[0];

strQuarterYear = strSplitQuarter[1];

}

else

{

strQuarterMonth = "";

strQuarterYear = "";

}

string PublishedDate = ds.Tables[0].Rows[i]["PublishedDate"].ToString();

PublishedDate = CleanDateField(PublishedDate).ToString();

string TentativeStartDate = ds.Tables[0].Rows[i]["TentativeStartDate"].ToString();

TentativeStartDate = CleanDateField(TentativeStartDate).ToString();

string TentativeEndDate = ds.Tables[0].Rows[i]["TentativeEndDate"].ToString();

TentativeEndDate = CleanDateField(TentativeEndDate).ToString();

UpdateEarnings((ds.Tables[0].Rows[i]["TickerName"]).ToString(), strQuarterMonth, strQuarterYear,

PublishedDate, TentativeStartDate, TentativeEndDate, ds.Tables[0].Rows[i]["Comments"].ToString());

}

}

else

{

//do nothing

}

OLEConn.Close();

System.IO.File.Delete(strTempPath + fluplEarningsFile.FileName.ToString());

AjaxControlToolkit.ToolkitScriptManager.RegisterStartupScript(this, typeof(string), "Success", "alert('The data has been imported successfully');", true);

}