c# - Local Time Format in Excel

Question

I'm using c# to import same data in Excel and on of the cell in each row has date time

var dataToImportToExcel= new object[1048576, 10] //Array of data
foreach(...)
{
...
     dataToImportToExcel[row, columnIndex++] = UnixTime.LocalTimeToDateTime(time)
...
}

Here UnixTime is defined as Epoch.AddMilliseconds(unixTimeStamp)

After creating the above variable it's passed to current worksheet

var writeRange = currentWorkSheet.Range[i, j];
writeRange.Value2 = dataToImportToExcel;

Excel is showing date time format as 02/05/2021 06:04:37.000 pm instead of 5/2/2021 6:04:35 pm, here later on is the local date time format. Even if I change local date time format in machine it always uses first format only. While debugging I can see in the IDE that date format is correctly showing in variable dataToImportToExcel

question from:https://stackoverflow.com/questions/66068061/local-time-format-in-excel

Answer 1

It looks your Excel understood that you wrote a DateTime value into the cell. It's format is not defined by the value but by the formatting information set on Cell or Range. Looking into How to make correct date format when writing data to Excel topic you should set it like this:

Range rg = (Excel.Range)worksheetobject.Cells[1,1];
// Instead of "MM/DD/YYYY" you may use a format specifier appropriate for your needs.
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";

This will format the entire column in the same way. Following the topic above you will find other examples like setting only one cell's format.