0
value = string.Empty;
if (cell.StyleIndex != null)
{
    CellFormat cf = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
    if (cf.NumberFormatId == 14)
    {
        //value = DateTime.Parse("1900-01-01").AddDays(int.Parse(cell.CellValue.Text) - 2).ToString("yyyy-MM-dd");
        value = Convert.ToString(DateTime.FromOADate(Double.Parse(cell.CellValue.Text)).ToShortDateString());
    }
    if (cf.NumberFormatId == 176 || cf.NumberFormatId == 177)
    {
        //value = DateTime.Parse("1900-01-01").AddDays(int.Parse(cell.CellValue.Text) - 2).ToString("yyyy-MM-dd");
        value = Convert.ToString(DateTime.FromOADate(Double.Parse(cell.CellValue.Text)));
    }
}

I am using the above code to check if there is any date column in excel and then convert the value of the cell to equivalent date.By default the NumberFormatID is 14,but there are more date formats in excel.For a particular date format sometimes the NumberFormatID is 165 and sometimes it is showing 176. These are not fixed.So when i am selecting the date time format sometimes it is 176 and sometimes it is different so my code is not working.How do i differentiate a particular date and datetime format?

Community
  • 1
  • 1
Pallav
  • 163
  • 2
  • 13
  • Do you need to check the cell format or are you just trying to parse Date/DateTime values? – mjw Aug 14 '15 at 13:40
  • Not sure if you can use the IsDate() VBA function but [this link](http://stackoverflow.com/a/17363466/4914662) might help – paul bica Aug 14 '15 at 19:37
  • @mjw I am trying to parse date/datetime values but need to check first if it is a date/datetime formatted cell because there can be other cells which may not have date values.According to this post http://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value?rq=1 the number format ID for mm/dd/yyyy format is 14 and all other date/datetime formats are in the range 14-22 but in my case 14 is default and other number format ID are >164 and they are not fixed. – Pallav Aug 15 '15 at 19:21

1 Answers1

0

Like you say, there is a lot of "dateformat", and people can custom the field (I don't know if it's the case for you).

If your only problematic is "parsing a date", simply use DateTime.TryParse

value = string.Empty;
if (cell.StyleIndex != null)
{
    DateTime res;
    if (DateTime.TryParse(cell.CellValue.Text, out res))
    {
        value = res.ToString("yyyy-MM-dd");
    }
}

If the cell is in a "date format", TryParse will succeed

Maxime Porté
  • 1,034
  • 8
  • 13
  • 1
    cell.CellValue.Text is always a double value so DateTime.TryParse is false everytime. Even if use DateTime.FromOADate to convert the double value to datetime then tryparse it returns true.Now the big question is how will know that the double value in the cell is a datetime and it needs to be converted to datetime that it is not a cell containing double that does not need datetime parsing??? – Pallav Sep 14 '15 at 10:13