How to present DateTime in Excel

Question

What is the best way to represent DateTime in Excel? We use Syncfusions Essential XlsIO to output values ​​to an Excel document that works great. But I can not figure out how to display DateTime in a column. Not when it is done directly directly in Excel. It's impossible? Should I use a separate date and time column? I am very sorry that I did not do this, because these are sort varieties, etc ... if Excel didn’t have something smart to fix it ...

Answer 1

The basic datatype for datetime in Excel is a 64-bit floating-point number, where the day length is January 1 and January 1, 1900 00:00, equal to 1. Thus, on June 11, 2009, 5:30 p.m. about 39975.72917.

If a cell contains a numeric value such as this, it can be converted to date-time simply by applying the datetime format to the cell.

So, if you can convert your dates to numbers using the above formula, output them to the appropriate cells and then set the cell formats to the appropriate date and time format, for example. yyyy-mm-dd hh: mm: ss, then it should be possible to get what you want.

Stefan de Bruijn also pointed out that there is a mistake in Excel that it mistakenly assumes that 1900 is a leap year, so you need to take this into account when calculating ( Wikipedia ).

Answer 2

If, like me, you cannot find the date and time in the format dialog box, you should find it in "Custom".

I just selected 'dd / mm / yyyy hh: mm' from 'Custom' and am pleased with the results.

Answer 3

You can do the following:

=Datevalue(text)+timevalue(text) .

Go to different types of date formats and select:

dd-mm-yyyy mm:ss am/pm .

Answer 4

Some versions of Excel do not have the date and time formats available in the standard selection lists, but you can simply enter a custom format string, such as yyyy-mm-dd hh: mm: ss by:

• Right Click -> Format Cells
• Number Tab
• Select a category Custom
• Enter a custom format string in the Type field

This works on my Excel 2010

Answer 5

Excel can display the date type in the same way as DateTime. Right-click on the affected cell, select "Format Cells", then in the "Date of Category Selection" and "Type" section select a type that looks something like this:

 3/14/01 1:30 PM 

This should do what you requested. I tested sorting by some data samples in this format and it seemed to work fine.

Answer 6

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

Answer 7

Excel expects dates and times to be stored as a floating point number, the value of which depends on the setting of the date of the 1904 workbook, as well as a number format such as "mm / dd / yyyy" or "hh: mm: ss" or "xx: mm: 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. Colors ["A1"]. Value = DateTime.Now;

This converts the DateTime to double, which is the base type that Excel uses for date / time, and then automatically formats the cell with the date date and / or temporary number depending on the value.

SpreadsheetGear also has the IWorkbook.DateTimeToNumber (DateTime) and NumberToDateTime (double) methods that convert from .NET DateTime objects to double that Excel can use.

I would expect XlsIO to have something like this.

Disclaimer: I have SpreadsheetGear LLC

Answer 8

You can set the time values ​​in 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 .