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...
Getting DATEPART in HQL or Criteria?
C# Dates - Databases, Display and Localization
Java equivalent to C#'s TimeSpan
So 11th June 2009 17:30 is about 39975.72917..
Get DateTime For Another Time Zone Regardless of Local Time Zone
If a cell contains a numeric value such as this, it can be converted to a datetime simply by applying a datetime format to the cell..
How can I subtract a day from a Python date?
So, if you can convert your datetimes to numbers using the above formula, output them to the relevant cells and then set the cell formats to the appropriate datetime format, e.g.
Unable to parse a DateTime
yyyy-mm-dd hh:mm:ss, then it should be possible to acheive what you want..
Also Stefan de Bruijn has pointed out that there is a bug in Excel in that it incorrectly assumes 1900 is a leap year so you need to take that into account when making your calculations (Wikipedia)..
How to format a DateTime like “Oct. 10, 2008 10:43am CST” in C#
- Right click -> Format Cells
- Number tab
- Choose Category Custom
- Enter your custom format string into the "Type" field
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:.
That should do what you requested.
3/14/01 1:30 PM
I tested sorting on some sample data with this format and it seemed to work fine..
You can find more information here. http://help.syncfusion.com/ug_73/xlsio/NumberFormatting.html.
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;@";