How to convert dates in .NET Excel Library

16 Aug 20268 minutes to read

The following code examples demonstrate converting text formatted date values to DateTime in Excel workbook using C# (Cross-platform and Windows-specific) and VB.NET.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    //Open the input workbook
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Input.xlsx"));

    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];

    //Get the used range to iterate through populated cells
    IRange used = worksheet.UsedRange;

    //Set culture and parsing styles for interpreting text dates
    CultureInfo culture = new CultureInfo("en-IN");
    DateTimeStyles styles = DateTimeStyles.None;

    //Iterate through the used range and convert text formatted dates to DateTime
    for (int row = used.Row; row <= used.LastRow; row++)
    {
        for (int col = used.Column; col <= used.LastColumn; col++)
        {
            IRange cell = worksheet[row, col];
            DateTime date;

            //Log if the cell already contains a true DateTime
            if (cell.HasDateTime)
            {
                Console.WriteLine(cell.DateTime);
            }
            //Try parsing text using the specified culture and assign DateTime back to the cell
            else if (DateTime.TryParse(cell.Value, culture, styles, out date))
            {
                cell.DateTime = date;
            }
        }
    }

    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;

    //Open the input workbook
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/Input.xlsx"));

    //Access the first worksheet
    IWorksheet worksheet = workbook.Worksheets[0];

    //Get the used range to iterate through populated cells
    IRange used = worksheet.UsedRange;

    //Set culture and parsing styles for interpreting text dates
    CultureInfo culture = new CultureInfo("en-IN");
    DateTimeStyles styles = DateTimeStyles.None;

    //Iterate through the used range and convert text formatted dates to DateTime
    for (int row = used.Row; row <= used.LastRow; row++)
    {
        for (int col = used.Column; col <= used.LastColumn; col++)
        {
            IRange cell = worksheet[row, col];
            DateTime date;

            //Log if the cell already contains a true DateTime
            if (cell.HasDateTime)
            {
                Console.WriteLine(cell.DateTime);
            }
            //Try parsing text using the specified culture and assign DateTime back to the cell
            else if (DateTime.TryParse(cell.Value, culture, styles, out date))
            {
                cell.DateTime = date;
            }
        }
    }

    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath(@"Output/Output.xlsx"));
}
Using excelEngine As New ExcelEngine()

    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx

    'Open the input workbook
    Dim workbook As IWorkbook = application.Workbooks.Open("Input.xlsx")

    'Access the first worksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Get the used range to iterate through populated cells
    Dim used As IRange = worksheet.UsedRange

    'Set culture and parsing styles for interpreting text dates
    Dim culture As New CultureInfo("en-IN")
    Dim styles As DateTimeStyles = DateTimeStyles.None

    'Iterate through the used range and convert text formatted dates to DateTime
    For row As Integer = used.Row To used.LastRow
        For col As Integer = used.Column To used.LastColumn

            Dim cell As IRange = worksheet(row, col)
            Dim parsedDate As DateTime

            'Log if the cell already contains a true DateTime
            If cell.HasDateTime Then
                Console.WriteLine(cell.DateTime)

                'Try parsing text using the specified culture and assign DateTime back to the cell
            ElseIf DateTime.TryParse(cell.Value, culture, styles, parsedDate) Then
                cell.DateTime = parsedDate
            End If

        Next
    Next

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")

End Using

A complete working example in C# is present on this GitHub page.