Get Date column value from Excel

Question

I have a project given to me to fix some issues there.

There is an Excel sheet that has a date column. Now I am checking if the date is valid with the below code. After the try, I keep getting an error

String '43941' was not recognized as a valid DateTime

I have tried some code on StackOverflow but none fix this problem for me.

public ValidationResult IsDateValid(ExcelWorksheet workSheet, int noOfRow)
{
    ValidationResult validationResult = new ValidationResult();
    validationResult.ErrorColIndex = 3;
    validationResult.IsValid = true;

    // be on the first column [r,c]
    int row = 2;

    for (int i = 2; i <= noOfRow; i++)   //start from the second row
    {
        if (!string.IsNullOrEmpty(workSheet.Cells[i, 3].Text))
        {
            string date = workSheet.Cells[i, 3].Value.ToString();

            // will throw exception if the fields in tenor are invalid
            try
            {
                DateTime p = DateTime.ParseExact(date.Trim(), "M/dd/yyyy", null);
            }
            catch (Exception ex)
            {
                validationResult.Message = "Invalid Date.";
                validationResult.IsValid = false;
                validationResult.ErrorRowIndex = row;
                break;
            }
        }
        else
        {
            validationResult.Message = "Empty Date.";
            validationResult.IsValid = false;
            validationResult.ErrorRowIndex = row;
            break;
        }

        ++row;
    }

    return validationResult;
}

I will be glad if someone can point out what I am doing wrong

Thanks

In what way do you not understand that '43941' is not a valid date format? It sounds to me like it's a 'number of days', value. — Neil, Apr 20 '20 at 15:41
But "43941" and "20/4/2020" are *not* the same format. Your code is *expecting* a value in the form "M/dd/yyyy", and the string "43941" definitely does not match that pattern. It sounds like you don't want to parse it as a date at all - you want to parse it as a *number* (integer or double) and then use `new DateTime(...).AddDays()` — Jon Skeet, Apr 20 '20 at 15:44
I've removed invalid ASP.Net Core tag from the post as it is not related to the question and Excel can't run on server properly. — Alexei Levenkov, Apr 20 '20 at 15:56

score 2 · Accepted Answer · answered Apr 20 '20 at 15:52

2

Hi techstack excel usees OLE Automation Date.

use DateTime.FromOADate(Double) Method

It Returns a DateTime equivalent to the specified OLE Automation Date.

answered Apr 20 '20 at 15:52

vrs

381
2
12

I suspect this is probably the correct answer – Neil Apr 20 '20 at 16:14

Get Date column value from Excel

1 Answers1