CODEWITHSUNDEEP
c#asp.netexcel
Windtalker
Windtalker

Reputation: 796

Read non regular time format from excel in C#

I am trying to read non regular time format from excel in C#, the time value in excel is as "29-Aug-01 11.23.00.000000000 PM", and in excel the cells format is 'regular' not 'time'.

Now I need read the time in excel then assign the time into calendar time in asp.net/c#, how can I let the program understand the time format?

Big thx!

my code does not work

DateTime expTime = DateTime.ParseExact(strDate, "dd-MMM-yy hh.mm.ss.fffffffff tt",      System.Globalization.CultureInfo.CurrentUICulture.DateTimeFormat);

Upvotes: 4

Views: 354

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

The original format string fails because the DateTime type can't represent nanoseconds. The following format string will work:

var expTime = DateTime.ParseExact(strDate, "dd-MMM-yy hh.mm.ss.fffffff00 tt", 
              CultureInfo.InvariantCulture);

This works because the 00 characters are interpreted as literals and ignored during parsing.

Note that I'm passing CultureInfo.InvariantCulture instead of the system's UI Culture. This ensures that the English month abbreviations will be used even if the user's UI Culture is set to another language. Otherwise, DateTime.ParseExact will try to parse the date string using the user's language month abbreviations

Upvotes: 0

Karol Marian Słuszniak
Karol Marian Słuszniak

Reputation: 335

I have similar solution, but more accur.

string s = "29-Aug-01 11.23.00.000000000 AM";

DateTimeOffset myDate = DateTimeOffset.ParseExact(
    s,
    "dd-MMM-yy hh.mm.ss.fffffff00 tt",
    System.Globalization.CultureInfo.InvariantCulture);

EDIT: You can't use more then 7 'f' chars.

Upvotes: 1

Related Questions