How to represent a DateTime in Excel

Question

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...

Answer 1

The underlying data type of a datetime in Excel is a 64-bit floating point number where the length of a day equals 1 and 1st Jan 1900 00:00 equals 1. So 11th June 2009 17:30 is about 39975.72917.

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.

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. yyyy-mm-dd hh:mm:ss, then it should be possible to achieve 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).