How to represent a DateTime in Excel


How to represent a DateTime in Excel



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?

1:



C# Dates - Databases, Display and Localization
The underlying datatype 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.


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..
How to validate a Datetime value in javascript?
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#


2:


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


3:


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.


4:


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:.
  1. Right click -> Format Cells
  2. Number tab
  3. Choose Category Custom
  4. Enter your custom format string into the "Type" field
This works on my Excel 2010.


5:


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


6:


dd-mm-yyyy hh:mm:ss.000 Universal sortable date/time pattern.


7:


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.


8:


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. http://help.syncfusion.com/ug_73/xlsio/NumberFormatting.html.



70 out of 100 based on 50 user ratings 850 reviews