Converting Excel date and time format into SAP format

[Firoz_Ashraf]

Hi,

We are reading external SQL database through our ABAP program.

Records are being fetched successfully. However, the records that we are getting contains date and time in the format 39934.02423, which corresponds to 01/05/2009 00:34:53 when formatted in excel.

The question is how do we convert this in our ABAP program so that we get '39934' as 01/05/2009 and the decimal part '02423' as 00:34:53 ?

Any help will be highly appreciated.

Warm Regards,

Firoz.

[Firoz_Ashraf]

Hello Firoz,

Try using the following code. I picked January 1, 2008 as my base date because if you use January 1, 1900 then the dates in 2009 will be off by a couple of days.

REPORT  zcalc_date_using_seconds.

CONSTANTS:  january_1_2008_in_secs(10) TYPE p VALUE '3408307200'.
CONSTANTS:  no_of_secs_in_day TYPE i VALUE '86400'.
CONSTANTS:  january_1_2008 TYPE sydatum VALUE '20080101'.
CONSTANTS:  time_0 TYPE syuzeit VALUE '000000'.
DATA:  requested_date_in_secs(10) TYPE p.
DATA:  diff_with_january_1_2008(10) TYPE p.
DATA:  requested_date TYPE sydatum,
       requested_time TYPE syuzeit.

START-OF-SELECTION.
  requested_date_in_secs = '39934.02423' * no_of_secs_in_day.
  diff_with_january_1_2008 = requested_date_in_secs - january_1_2008_in_secs.

  CALL FUNCTION 'C14Z_CALC_DATE_TIME'
    EXPORTING
      i_add_seconds = diff_with_january_1_2008
      i_uzeit       = time_0
      i_datum       = january_1_2008
    IMPORTING
      e_datum       = requested_date
      e_uzeit       = requested_time.

  WRITE:  /'Requested date:  ', requested_date.
  WRITE:  /'Requested time:  ', requested_time.

Edited by: Rae Ellen Woytowiez on Dec 29, 2010 6:26 PM

[Former Member]

Hello Firoz,

Are you reading from an Excel spreadsheet? If so, then perhaps you can change the column in the spreadsheet to text.

Kind Regards,

Rae Ellen Woytowiez

[kesavadas_thekkillath]

Sounds basic if my understanding is correct.

You can search for INVERTED TIME & INVERTED DATE . You will get some function modules.

[Former Member]

Hello Firoz,

Try using the following code. I picked January 1, 2008 as my base date because if you use January 1, 1900 then the dates in 2009 will be off by a couple of days.

REPORT  zcalc_date_using_seconds.

CONSTANTS:  january_1_2008_in_secs(10) TYPE p VALUE '3408307200'.
CONSTANTS:  no_of_secs_in_day TYPE i VALUE '86400'.
CONSTANTS:  january_1_2008 TYPE sydatum VALUE '20080101'.
CONSTANTS:  time_0 TYPE syuzeit VALUE '000000'.
DATA:  requested_date_in_secs(10) TYPE p.
DATA:  diff_with_january_1_2008(10) TYPE p.
DATA:  requested_date TYPE sydatum,
       requested_time TYPE syuzeit.

START-OF-SELECTION.
  requested_date_in_secs = '39934.02423' * no_of_secs_in_day.
  diff_with_january_1_2008 = requested_date_in_secs - january_1_2008_in_secs.

  CALL FUNCTION 'C14Z_CALC_DATE_TIME'
    EXPORTING
      i_add_seconds = diff_with_january_1_2008
      i_uzeit       = time_0
      i_datum       = january_1_2008
    IMPORTING
      e_datum       = requested_date
      e_uzeit       = requested_time.

  WRITE:  /'Requested date:  ', requested_date.
  WRITE:  /'Requested time:  ', requested_time.

Edited by: Rae Ellen Woytowiez on Dec 29, 2010 6:26 PM

[Firoz_Ashraf]

Thanks Rae. It solved my problem.

[Former Member]

Hi Rae,

I have got similar issue. Could you please help me out in the below thread:

http://scn.sap.com/message/14343971

Thanks & Regards,

Prashant

[Former Member]

Hi,

It is 0-Jan-00 + 39934 and 0:00:00 + .02423. So in the excel if you change the format that is the formula that would be applied.If in the program you are getting those values for date fields you can use the above logic to convert to the date and time.

Regards,

Himanshu Verma