Excel Time Format

Question

How to make a time value international readable.

I use C++ to export a time value (digital) to a Excel Cell, It is like such format:41346,8918485185, this value correponds to “13.03.2013  21:24:16” when i using "yyyy-mm-dd hh:mm:ss" to format this cell. But this view will get changed when I change my "Formats" under "Region and Language" in system control panel, say "Germany", It will show "yyyy-24-dd 21". I find if i want to sustain the same view, i need to adjust customized format like "JJJJ-MM-TT h:mm:ss".

Is there any possible to export and format a date/time value to localized view? or even a readable view?

Pls kindly suggest.

Answer 1

Hi,

Whatever you do, the display will change according to the regional settings if you leave the format as General or date/time. The only way to circumnavigate this is to use a string/text format for that cell..

regards

JY

Answer 2

see section: "using strings as worksheet function arguments" in this page from Ron de Bruin site:

http://www.rondebruin.nl/win/s9/win013.htm

hth,

hector.

Answer 3

I find that "yyyy-mm-dd hh:mm:ss" is the most format way to format this DateTime value, "JJJJ-MM-TT h:mm:ss" is the corresponding format under "German/Germany" and "jj/mm/aaaa hh:mm:ss" under "French/France". 

So, is there any way to get these format strings when i retrieve system location?

Answer 4

Thank you very much. 

Same solution I use program to set the format of this column into "yyyy-mm-dd hh:mm:ss", this will take effection under many contexts. But when you switch your Region into some Europe contries, this will display differently. Like "Germany" for example, this format will bring out  "yyyy-24-dd 21:24:16", not readable really.

Answer 5

I copied you number to A1 (replacing the comma by a period, since that is what I used for the decimal point)

Then in B1 I entered =TEXT(A1,"yyyy-mm-dd hh:mm:ss")

This displayed 2013-03-13 12:24:16

best wishes