0

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

Austin
  • 29
  • 6
  • I do not think that `UnixTime` is standard available in Excel, how did you define it? My guess is that it returns a string, and not a datetime. – Luuk Feb 05 '21 at 17:47
  • Sorry it's defined as `Epoch.AddMilliseconds(unixTimeStamp)` – Austin Feb 05 '21 at 18:01

1 Answers1

0

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.

cly
  • 661
  • 3
  • 13
  • I don't want to restrict to specific format, if local format changes I want to use that. I want to show date time format same as local date time format selected in machine – Austin Feb 05 '21 at 18:10