CODEWITHSUNDEEP
c#
ServerMonkey
ServerMonkey

Reputation: 1154

DateTime format and excel

I'm parsing an Excel created csv file and have found that dates in the file have been specified in the following format: mm:ss.0

Based on the assumption that the format is not a ISO standard I believe this can be read with DateTime.FromOADate but so far I've not been able to achieve this without receiving the following error: "The string was not in a correct format"

Example file entry: 59:03.0

Here's what I've tried:

foreach (var rec in record)
{
     double temp = double.Parse(rec.date, System.Globalization.NumberStyles.AllowDecimalPoint, System.Globalization.NumberFormatInfo.InvariantInfo);        
     MessageBox.Show(DateTime.FromOADate(temp).ToString());
}

Any assistance would be appreciated.

Upvotes: 0

Views: 186

Answers (2)

fyjham
fyjham

Reputation: 7034

Use ParseExact, then you can specify the format and literally tell it a format of "mm:ss.0".

EG: DateTime date = DateTime.ParseExact(rec.date, "mm:ss.0", null);

You may want to check the format you get from Excel though, because depending on the spreadsheet sometimes you'll get a string, sometimes a DateTime and sometimes a number (which can be passed to the FromOADate method). But if it's in the format you describe the above should work.

Note: Without specifying any date part for your datetime, it'll get today's date.

http://msdn.microsoft.com/en-us/library/w2sa9yss%28v=vs.110%29.aspx

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You can try to parse this format manually

string inputString = "59:03.0";
string[] split = inputString.Split(new char[] { ':', '.' });

DateTime dateTime = new DateTime(1, 1, 1, 0, int.Parse(split[0]), int.Parse(split[1]));

Upvotes: 0

Related Questions