How to set and format time values in Excel using TimeSpan?

8 May 20254 minutes to read

You can set a TimeSpan value into an Excel worksheet cell and format it to display as time using the Syncfusion XlsIO library. The following code examples demonstrate how to do this in C# (cross-platform and Windows-specific) and VB.NET.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];

    TimeSpan ts = new TimeSpan(12, 32, 38);

    //Convert the TimeSpan to a fractional day value that Excel understands
    double excelTimeValue = ts.TotalDays;

    //Set value in cell
    sheet.SetValueRowCol(excelTimeValue, 1, 1);

    //Apply the time format to the cell to display it as 'hh:mm:ss'
    sheet.Range[1, 1].NumberFormat = "hh:mm:ss";

    #region Save
    //Saving the workbook
    FileStream outputStream = new FileStream(Path.GetFullPath("Output.xlsx"), FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);
    #endregion    

    //Dispose streams
    outputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine engine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];

    TimeSpan ts = new TimeSpan(12, 32, 38);

    //Convert the TimeSpan to a fractional day value that Excel understands
    double excelTimeValue = ts.TotalDays;

    //Set value in cell
    sheet.SetValueRowCol(excelTimeValue, 1, 1);

    //Apply the time format to the cell to display it as 'hh:mm:ss'
    sheet.Range[1, 1].NumberFormat = "hh:mm:ss";

    //Saving the workbook
    workbook.SaveAs("Output.xlsx"); 
}

VB.NET [Windows-specific]

Using engine As New ExcelEngine()
    Dim application As IApplication = engine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim sheet As IWorksheet = workbook.Worksheets(0)    
    Dim ts As New TimeSpan(12, 32, 38)

    'Convert the TimeSpan to a fractional day value that Excel understands
    Dim excelTimeValue As Double = ts.TotalDays

    'Set value in cell
    sheet.SetValueRowCol(excelTimeValue, 1, 1)

    'Apply the time format to the cell to display it as 'hh:mm:ss'
    sheet.Range(1, 1).NumberFormat = "hh:mm:ss"

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

A complete working example to set and format time values in Excel using TimeSpan is available on this GitHub page.