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

Guddle

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

Manrope

Unfortunately that's incorrect. If cell A2 = 1-1-1900 and you do Value(A2), you get '1'. Also, Excel incorrectly assumes that 1900 is a leap year (in all calculations). In other words, you probably need to subtract 2 days to get the correct date when working in your favorite programming language... – Besmear

Thanks Stefan de Bruijn, I've corrected the answer and added a note about the leap year bug. – Manrope

Be careful, datetime format depends on Excel language. For example, in Spanish should be: aaaa-mm-dd hh:mm – Muzz

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

Idiomorphic

The above doesn't appear to work for Excel for Mac :( – Byer

@BrantleyBeaird I don't have a Mac, but can't you type 'dd/mm/yyyy hh:mm' into the custom string? Screenshot: imgur.com/a/51ipd – Idiomorphic

I did that, then added ":ss" to include seconds. – Phototaxis

You can also edit the custom one by following the example it sets: I modified mine to yyyy/mm/dd hh/mm/ss to get an output of 2021/07/26 13:51:37 – Tibbetts

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

Crystacrystal

Why do you need to do Datevalue(text)+timevalue(text)? Just set the formatting in the cell. – Idiomorphic

Answer 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

Mamiemamma

I'd already said that in an answer - https://mcmap.net/q/224851/-how-to-represent-a-datetime-in-excel... – Idiomorphic

Answer 5

One of the Simple ways is:

=TEXT(DATE(2023,6,21)+TIME(16,0,0), "dd-MMM-yyyy hh:mm")

It represents: 21-Jun-2023 16:00 (It also calculates algorithm itself)

syntaxt of TEXT() function:

TEXT(number, format)

Cellar

Answer 6

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.

Nephology

That is actually true... do you know if it is possible to set this in a culture spesific way from C#? Does the Standard DateTime Format Strings from .NET work in excel? – Guddle

If the data is exported from .NET to Excel as a string, and it looks something similar to my example, it should work when you format the cells. As for culture-specificity (?), there is a ton of custom formats (i.e. Category is Custom in Excel) that might provide what you are looking for. – Nephology

The version of Excel I have (2003) does not have a date option with time – Zenaidazenana

If your version of Excel doesn't come with a standard date-time format you can enter it as a custom field as explained in my answer below. – Mamiemamma

I have it in Excel 2013, but I had to select "English (United States)" in the Locale box underneath the Type section first. – Heideheidegger

Answer 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

Series

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

Ejecta

The link is (for the latest docs) help.syncfusion.com/ug_94/Reporting/XlsIO/ASP.NET/… – Nauplius

Answer 9

So I've been battling with this issue all day.

Basically I have it now sorted and part of the solution was a code that Excel itself generated which is:

[$-en-AU]yyyy-mm-dd hh:mm

So, in the first instance,

• in a new spreadsheet, type your entry in as per usual, eg: 2026-01-31 10:00
• set the format of the cell to "custom" and use the above formula, ie [$-en-AU]yyyy-mm-dd hh:mm
• Hit enter and Bob's your uncle!

Then save it as a CSV file, then close it and re-open it to check Excel hasn't changed the date format.

The weird thing is if it does work (and I've re-tried it a few times successfully), when you check the format of the cell you've created, it's changed the format to "General".

But seriously who cares. As long as it works!!

You can then copy and paste the cell and use it wherever you want!!

I hope this solution works for you!!

Regards

Richard

Talavera

Answer 10

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

Alyosha

Do you actually mean yyyy-mm-dd hh:mm:ss.000? There's also ISO8601 format which is similar: YYYY-MM-DDThh:mm:ssTZD (The limitations of these are, though sortable, they are still strings, so you can't apply Excel date functions to them) – Truism

Yes, you right, and with capitalized HH to 24hours format. yyyy-mm-dd HH:mm:ss.000 – Alyosha

Downvoted because I don't think this is very complete or correct as an answer, sorry. – Idiomorphic

This is not sortable or universal. You need to put the year first for both. – Brockbrocken