Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Integer to DateTime format convert method


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Aug 29, 2015 2:48 PM Last Post By: Patricio Cerda
Patricio Cerda

Posts: 122
Registered: 3/13/01
Integer to DateTime format convert method  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 28, 2015 9:06 AM
Hi,

I'm working on C++ Builder XE to importing Excel spreadsheets data, but sometimes the date cells comes with its values on integer format, for example the value of a cell is 42186 that represents '07-01-2015'. I don't know how to modify the cells format before to import the value to receive '07-01-2015'.

Is there a way to made a format conversion directly on C++ Builder, from an Integer value to a DateTime expression (from 42186 to '07-01-2015') ?
I founded the Int() operator of TDateTime class, but it does the opposite format conversion, no what I want.

Regards,
Patricio Cerda
Remy Lebeau (Te...


Posts: 9,447
Registered: 12/23/01
Re: Integer to DateTime format convert method  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 28, 2015 11:49 PM   in response to: Patricio Cerda in response to: Patricio Cerda
Patricio wrote:

Is there a way to made a format conversion directly on C++ Builder,
from an Integer value to a DateTime expression (from 42186 to
'07-01-2015') ?

You can usually just assign the Excel integer as-is directly to a TDateTime,
which has a constructor to accept integer input:

int value = 42186;
TDateTime dt = value;
ShowMessage(dt.DateString()); // shows July 1 2015 (in the current locale)


The integral portion of a TDateTime is the number of days since December
30 1899, and the fractional portion represents the time within a 24hour day.
Excel uses the same internal format for its date/time values (see http://www.cpearson.com/excel/datetime.htm),
however there are some subtle differences (which you probably won't run across):

1. TDateTime supports dates before December 30 1899, but Excel does not.

2. In TDateTime, integer 0 represents December 30 1899. In Excel, integer
0 represents January 0 1900 (and no, day 0 is NOT a typo).

3. Excel supports February 29 1900, though 1900 was not a leap year.

Pretty much, as long as you are working with dates that are March 1 1900
or later, you should be fine.

--
Remy Lebeau (TeamB)
Patricio Cerda

Posts: 122
Registered: 3/13/01
Re: Integer to DateTime format convert method  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 29, 2015 2:48 PM   in response to: Remy Lebeau (Te... in response to: Remy Lebeau (Te...
Thanks Remy,

You can usually just assign the Excel integer as-is directly to a TDateTime,
which has a constructor to accept integer input:

int value = 42186;
TDateTime dt = value;
ShowMessage(dt.DateString()); // shows July 1 2015 (in the current locale)

It is very simple and works perfectly.

Best regards,
Patricio Cerda
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02