What is the best way of representing a DateTime
in Excel? We use Syncfusions Essential XlsIO to output values to an Excel document which works great. But I can't figure out how to display a DateTime
in a column. Not when doing it myself directly in Excel either. Is it impossible? Do I have to use a separate date and a time column? I really wish I didn't, cause it kind of breaks sorting etc... unless Excel have something clever going on to fix that...
You can do the following:
=Datevalue(text)+timevalue(text)
.
Go into different types of date formats and choose:
dd-mm-yyyy mm:ss am/pm
.
Some versions of Excel don't have date-time formats available in the standard pick lists, but you can just enter a custom format string such as yyyy-mm-dd hh:mm:ss by:
This works on my Excel 2010
Excel expects dates and times to be stored as a floating point number whose value depends on the Date1904 setting of the workbook, plus a number format such as "mm/dd/yyyy" or "hh:mm:ss" or "mm/dd/yyyy hh:mm:ss" so that the number is displayed to the user as a date / time.
Using SpreadsheetGear for .NET you can do this: worksheet.Cells["A1"].Value = DateTime.Now;
This will convert the DateTime to a double which is the underlying type which Excel uses for a Date / Time, and then format the cell with a default date and / or time number format automatically depending on the value.
SpreadsheetGear also has IWorkbook.DateTimeToNumber(DateTime) and NumberToDateTime(double) methods which convert from .NET DateTime objects to a double which Excel can use.
I would expect XlsIO to have something similar.
Disclaimer: I own SpreadsheetGear LLC
Excel can display a Date type in a similar manner to a DateTime. Right click on the affected cell, select Format Cells, then under Category select Date and under Type select the type that looks something like this:
3/14/01 1:30 PM
That should do what you requested. I tested sorting on some sample data with this format and it seemed to work fine.
dd-mm-yyyy hh:mm:ss.000 Universal sortable date/time pattern
If, like me, you can't find a datetime under date or time in the format dialog, you should be able to find it in 'Custom'.
I just selected 'dd/mm/yyyy hh:mm' from 'Custom' and am happy with the results.
You can set date time values to a cell in XlsIO using one of these options
sheet.Range["A1"].Value2 = DateTime.Now;
sheet.Range["A1"].NumberFormat = "dd/mm/yyyy";
sheet.Range["A2"].DateTime = DateTime.Now;
sheet.Range["A2"].NumberFormat = "[$-409]d-mmm-yy;@";
You can find more information here.
Source: Stackoverflow.com