날짜 및 시간 엑셀 업로드 데이터 컨버전

화가나는 엑셀데이터 간단하게 바꿔치기

엑셀 업로드 할 때 다음과 같은 로직을 통해 엑셀의 숫자값을 날짜/시간으로 변경할 수 있다.

사용자에게 제공하는 프로그램보다, 내가 마이그레이션 하다가 열받아서 하는 기록

DATA: lv_date(100),
       lv_time(100),
       lv_result(4).
 
 lv_date = '45329'.
 lv_time = '0.291840277777778'.
 
 PERFORM date_validation CHANGING lv_date lv_result.
 PERFORM time_validation CHANGING lv_time lv_result.
 
 
 BREAK-POINT.
 
 
 *&---------------------------------------------------------------------*
 *&      Form  DATE_VALIDATION
 *&---------------------------------------------------------------------*
 *       text
 *----------------------------------------------------------------------*
 *      -->P_<LV_VAL>  text
 *      <--P_LV_TYPE  text
 *----------------------------------------------------------------------*
 FORM date_validation  CHANGING    pv_val
                                   pv_result.
 
   DATA lv_datum TYPE sy-datum.
 
   CLEAR pv_result.
 
   REPLACE ALL OCCURRENCES OF: '-' IN pv_val WITH '',
                               '.' IN pv_val WITH '',
                               '/' IN pv_val WITH ''.
   CONDENSE pv_val NO-GAPS.
 
   IF strlen( pv_val ) = 5.
     lv_datum = '19000101'.
     lv_datum = lv_datum + pv_val - 2.
   ELSE.
     lv_datum = pv_val.
   ENDIF.
 
   CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'
     EXPORTING
       date                      = lv_datum
     EXCEPTIONS
       plausibility_check_failed = 1
       OTHERS                    = 2.
 
   IF sy-subrc = 0.
     pv_val = lv_datum.
   ELSE.
     pv_result = 'FAIL'.
   ENDIF.
 
 ENDFORM.
 *&---------------------------------------------------------------------*
 *&      Form  TIME_VALIDATION
 *&---------------------------------------------------------------------*
 *       text
 *----------------------------------------------------------------------*
 *      -->P_<LV_VAL>  text
 *      <--P_LV_TYPE  text
 *----------------------------------------------------------------------*
 FORM time_validation  CHANGING    pv_val
                                   pv_result.
 
   DATA: lv_uzeit   TYPE sy-uzeit,
         lv_type(4).
 
   CLEAR pv_result.
 
   IF pv_val = '0'.
     CLEAR pv_val.
     EXIT.
   ENDIF.
 
   IF pv_val(2) = '0.'.
     PERFORM set_tformat USING pv_val.
   ELSE.
     REPLACE ALL OCCURRENCES OF: ':' IN pv_val WITH ''.
   ENDIF.
 
   CONDENSE pv_val NO-GAPS.
   lv_uzeit = pv_val.
 
   PERFORM numeric_check   CHANGING pv_val lv_type.
 
   IF lv_type = 'NUMC'.
     IF lv_uzeit+4(2) = '60'.
       ADD 0 TO lv_uzeit.
     ENDIF.
   ELSE.
     pv_result = 'FAIL'.
     EXIT.
   ENDIF.
 
   CALL FUNCTION 'TIME_CHECK_PLAUSIBILITY'
     EXPORTING
       time                      = lv_uzeit
     EXCEPTIONS
       plausibility_check_failed = 1
       OTHERS                    = 2.
 
   IF sy-subrc = 0.
     pv_val = lv_uzeit.
   ELSE.
     pv_result = 'FAIL'.
   ENDIF.
 
 ENDFORM.
 *&---------------------------------------------------------------------*
 *&      Form  SET_TFORMAT
 *&---------------------------------------------------------------------*
 *       text
 *----------------------------------------------------------------------*
 *      -->P_PV_VAL  text
 *----------------------------------------------------------------------*
 FORM set_tformat  USING    pv_val.
 
   DATA : lv_h     TYPE n LENGTH 2,
          lv_m     TYPE n LENGTH 2,
          lv_s     TYPE n LENGTH 2,
          lv_uzeit TYPE sy-uzeit,
          lv_num   TYPE float.
 
   lv_num = pv_val.
   lv_num = pv_val * 24 .
   lv_h   = floor( lv_num ).
   lv_num = lv_num - lv_h.
   lv_num = lv_num * 60.
   lv_m   = floor( lv_num ).
   lv_num = lv_num - lv_m.
   lv_num = lv_num * 60.
   lv_s   = lv_num.
   pv_val = lv_h && lv_m && lv_s.
 
 ENDFORM.
 *&---------------------------------------------------------------------*
 *&      Form  NUMERIC_CHECK
 *&---------------------------------------------------------------------*
 *       text
 *----------------------------------------------------------------------*
 *      -->P_<LV_VAL>  text
 *----------------------------------------------------------------------*
 FORM numeric_check  CHANGING pv_val
                              pv_type TYPE datatype_d.
 
   CLEAR pv_type.
 
   REPLACE ALL OCCURRENCES OF '.' IN pv_val WITH space.
   REPLACE ALL OCCURRENCES OF ',' IN pv_val WITH space.
   CONDENSE pv_val NO-GAPS.
 
   CALL FUNCTION 'NUMERIC_CHECK'
     EXPORTING
       string_in = pv_val
     IMPORTING
       htype     = pv_type.
 
   IF pv_type <> 'NUMC'.
     pv_type = 'FAIL'.
   ENDIF.
 
 ENDFORM.