Converting between AM/PM and 24-hour time

[Richard Ristow]

One sometimes encounters input data with times given as 12-hour time
plus an AM/PM indicator(*). It's commonly desirable to convert these
to true SPSS ("24-hour") time values; the reverse transformation,
calculating 12-hour times with AM/PM from SPSS time values, is also
occasionally needed.

The conversion logic is not immediately intuitive: there are four
time intervals (midnight to 1:00 AM, 1:00 AM to noon, noon to 1:00
PM, and 1:00 PM to midnight) that must be treated individually. So it
seems worth while, for future reference, to post suggested methods
for both conversions. All the following code is tested.  In the examples,
. "Time" is the SPSS time, assumed less than 24 hours
. "Time12" is a time value, the (12-hour) time portion of the AM/PM form
. "AmPm" is a two-character string, to have values AM or PM
. Scratch variables like "#12hrs" are used as 'constant' values. See
Appendix A for how to define these; or, replace them by equivalent expressions:
#12hrs <==> TIME.HMS(12) <==> 12*60*60 <==> 43200
etc.

(*) For example, recent thread "Converting mm/dd/yyyy hh:mm:ss Excel
to SPSS", begun Tue, 25 Nov 2014 13:06:59 -0700.


CONVERTING FROM AM/PM FORM TO SPSS TIME
---------------------------------------
Below is what I'd usually recommend. It's not compact, but I think it
makes the logic very clear. It will give a missing result if there's
an error in the AmPm value:

DO IF    UPCASE(AmPm) EQ 'AM'.
.  DO IF Time12 LT #12hrs.
.     COMPUTE Time = Time12.
.  ELSE.
.     COMPUTE Time = Time12 - #12hrs.
.  END IF.
ELSE IF  UPCASE(AmPm) EQ 'PM'.
.  DO IF Time12 LT #12hrs.
.     COMPUTE Time = Time12 + #12hrs.
.  ELSE.
.     COMPUTE Time = Time12.
.  END IF.
END IF.

However, it can be done in a single COMPUTE, though without error
checking on the AmPm value:

COMPUTE Time = MOD(Time12,#12hrs) + #12hrs*(AmPm EQ 'PM').


CONVERTING FROM SPSS TIME TO AM/PM FORM
---------------------------------------
I'd recommend the following, clearly laying out the treatment of the
four time intervals. (Variable "AmPm" must be previously declared.):

DO IF   Time LT  #1hr.
.  COMPUTE AmPm    = 'AM'.
.  COMPUTE Time12  = Time + #12hrs.
ELSE IF Time LT  #12hrs.
.  COMPUTE AmPm    = 'AM'.
.  COMPUTE Time12  = Time.
ELSE IF Time LT  #13hrs.
.  COMPUTE AmPm    = 'PM'.
.  COMPUTE Time12  = Time.
ELSE.
.  COMPUTE AmPm    = 'PM'.
.  COMPUTE Time12  = Time - #12hrS.
END IF.

But it can be done in two lines -- a triumph, I'd say, of compactness
and inscrutability:

COMPUTE AmPm   = SUBSTR("AMPM",1+2*(Time GE #12hrs),2).
COMPUTE Time12 = #1hr  + MOD(Time+#11hrs,#12hrs).
============================
Appendix A: Pseudo-constants
============================
Scratch variables initialized to certain useful time values can be
created by inserting the following code at the beginning of the
transformation program:

Used in converting AM/PM form to SPSS time:
NUMERIC #12hrs     (TIME10).
DO IF   $CASENUM EQ 1.
.  COMPUTE #12hrs = TIME.HMS(12,0,0).
END IF.

Used in converting SPSS time to AM/PM form:
NUMERIC #1hr     #11hrs
         #12hrs   #13hrs        (TIME10).
DO IF   $CASENUM EQ 1.
.  COMPUTE #1hr   = TIME.HMS( 1,0,0).
.  COMPUTE #11hrs = TIME.HMS(11,0,0).
.  COMPUTE #12hrs = TIME.HMS(12,0,0).
.  COMPUTE #13hrs = TIME.HMS(13,0,0).
END IF.
===================================================
Appendix B: Test data generation, and all test code
===================================================
*  C:\Documents and Settings\Richard\My Documents                   .
*    \Technical\spssx-l\Z-2014\                                     .
*    2014-12-27 Ristow-Converting between AMPM and 24-hour time.SPS .

*  Test and demonstration of techniques for converting between time .
*  values and 12-hour times with AM/PM fields:                      .
*                                                                   .
*  I.   Creates a dataset of 50 time points as date-time values;    .
*  II.  Converts those values to strings in form                    .
*       "mm/dd/yyyy hh:mm:ss {AM|PM}";                              .
*  III. Reads those strings, parses them, and recovers the original .
*       date-time values.                                           .
*                                                                   .
*  Step II.  requires converting 24-hour time to AM/PM form;        .
*            three methods are given.                               .
*  Step III. requires converting AM/PM time to 24-hour time;        .
*            two methods are given.                                 .
*                                                                   .
*  See thread "Converting mm/dd/yyyy hh:mm:ss Excel to SPSS",       .
*  especially posting                                               .
*     Date:     Wed, 3 Dec 2014 22:04:08 -0500                      .
*     From:     Richard Ristow <[hidden email]>            .
*     Subject:  Re: Converting mm/dd/yyyy hh:mm:ss Excel to SPSS    .
*     Comments: To: David Marso <[hidden email]>             .
*     To:       [hidden email]                            .

*  ................................................................ .
*  ...   Part I:     Test data                                  ... .
*  ...   Fifty time points, independently uniformly distributed ... .
*  ...   through year 2012.                                     ... .

SET RNG = MT       /* 'Mersenne twister' random number generator */ .
SET MTINDEX = 3751 /* Boston, MA, telephone book                 */ .

INPUT PROGRAM.
.  NUMERIC CaseID (N3).
.  NUMERIC #Begin #End (DATETIME22).
.  COMPUTE #Begin = DATE.MDY(1,1,2012).
.  COMPUTE #End   = DATE.MDY(1,1,2013).
.  NUMERIC Instant (DATETIME22).
.  LOOP CaseID = 1 TO 50.
.     COMPUTE Instant = TRUNC(RV.UNIFORM(#Begin,#End)).
.     END CASE.
.  END LOOP.
.  VARIABLE WIDTH CaseID  (06).
.  VARIABLE WIDTH Instant (20).
.  VARIABLE ALIGN Instant (CENTER).
END FILE.
END INPUT PROGRAM.

EXECUTE       /* to populate the active dataset */.
DATASET NAME     TestData WINDOW=FRONT.

*  ................................................................ .
*  ...   Part II:   Date-time to string, in form                ... .
*                   "mm/dd/yyyy hh:mm:ss {AM|PM}"               ... .

ADD FILES
    /FILE=TestData.

*  ...  Four pseudo-constants (scratch variables loaded with    ... .
*  ...  the desired values) for use in the conversion logic.    ... .

NUMERIC #1hr     #11hrs
         #12hrs   #13hrs     (TIME10).
DO IF   $CASENUM EQ 1.
.  COMPUTE #1hr   = TIME.HMS( 1,0,0).
.  COMPUTE #11hrs = TIME.HMS(11,0,0).
.  COMPUTE #12hrs = TIME.HMS(12,0,0).
.  COMPUTE #13hrs = TIME.HMS(13,0,0).
END IF.

*  ...  Target variables, into which to convert date and time   ... .

NUMERIC @Date            (ADATE10).
NUMERIC @Time            (TIME10) .
NUMERIC @Time12          (TIME10) .
STRING  @AmPm            (A2)     .
NUMERIC @Time12b         (TIME10) .
STRING  @AmPmB           (A2)     .
NUMERIC @Time12c         (TIME10) .
STRING  @AmPmC           (A2)     .

VARIABLE WIDTH @Date (10).
VARIABLE WIDTH @Time @Time12 @Time12b @Time12c (8).

*  ...  Separate the Instant into date and time parts           ... .

COMPUTE @Date = XDATE.DATE(Instant).
COMPUTE @Time = XDATE.TIME(Instant).

*  ...  Convert (24-hr) time to AM/PM form, three ways          ... .

*       Method A: Straightforward, but verbose                  ... .

DO IF   @Time LT  #1hr.
.  COMPUTE @AmPm    = 'AM'.
.  COMPUTE @Time12  = @Time + #12hrs.
ELSE IF @Time LT  #12hrs.
.  COMPUTE @AmPM    = 'AM'.
.  COMPUTE @Time12  = @Time.
ELSE IF @Time LT  #13hrs.
.  COMPUTE @AmPm    = 'PM'.
.  COMPUTE @Time12  = @Time.
ELSE.
.  COMPUTE @AmPm    = 'PM'.
.  COMPUTE @Time12  = @Time - #12hrS.
END IF.

*       Method B: More compact                                  ... .

DO IF   @Time LT #12hrs.
.  COMPUTE @AmPmB = 'AM'.
ELSE.
.  COMPUTE @AmPmB = 'PM'.
END IF.

COMPUTE @Time12b = MOD(@Time,#12hrs).
IF      @Time12b  LT #1hr
         @Time12b = @Time12b + #12hrs.


*       Method C: In two statements, concise and inscrutable    ... .

COMPUTE @AmPmC   = SUBSTR("AMPM",1+2*(@Time GE #12hrs),2).
COMPUTE @Time12c = #1hr  + MOD(@Time+#11hrs,#12hrs).


*  ...  The date-time value "Instant" as a character string,    ... .
*       in the form it's likely to have in a .CSV file          ... .

STRING         DT(A22).
VARIABLE WIDTH DT(18).
COMPUTE DT = CONCAT(STRING(@Date,  ADATE10),' ',
                     STRING(@Time12,TIME08 ),' ',
                            @AmPm               ).


EXECUTE       /* to populate the active dataset */.
DATASET NAME     To12hrs  WINDOW = FRONT.


*  ...  Error-checking: Check that all converted values match   ... .

TEMPORARY.
SELECT IF       @Time12 NE @Time12b
              OR @Time12 NE @Time12c
              OR @AmPm   NE @AmPmB
              OR @AmPm   NE @AmPmC.

LIST /VARIABLES=CaseID   Instant
                 @Time
                 @Time12  @AmPm
                 @Time12b @AmPmB
                 @Time12c @AmPmC.

*  ................................................................ .
*  ...   Part III:  String mm/dd/yyyy hh:mm:ss AM to date-time  ... .

ADD FILES
    /FILE=To12hrs
    /KEEP=CaseID DT.

NUMERIC Date    (ADATE10)
         @Time12 (TIME10).
STRING  @AmPm   (A2).
NUMERIC Time
         TimeB   (TIME10)
         InstRcvr(DATETIME22).


*  ...  Parse variable "DT" into date, time, and AM/PM          ... .

STRING  #Buffer
         #Token  (A22).
NUMERIC #BreakPt(F3).

COMPUTE #Buffer  = LTRIM(DT).
COMPUTE #BreakPt = INDEX(#Buffer,' ').
COMPUTE #Token   = SUBSTR(#Buffer,1,#BreakPt).
COMPUTE Date     = NUMBER(#Token,ADATE10).

COMPUTE #Buffer  = SUBSTR(#Buffer,#BreakPt).
COMPUTE #Buffer  = LTRIM(#Buffer).
COMPUTE #BreakPt = INDEX(#Buffer,' ').
COMPUTE #Token   = SUBSTR(#Buffer,1,#BreakPt).
COMPUTE @Time12  = NUMBER(#Token,TIME10).

COMPUTE #Buffer  = SUBSTR(#Buffer,#BreakPt).
COMPUTE #Buffer  = LTRIM(#Buffer).
COMPUTE #BreakPt = INDEX(#Buffer,' ').
COMPUTE #Token   = SUBSTR(#Buffer,1,#BreakPt).
COMPUTE @AmPm    = #Token.


*  ...  A pseudo-constant (a scratch variable loaded with the   ... .
*  ...  desired value) for use in the conversion below.         ... .

NUMERIC #12hrs     (TIME10).
DO IF   $CASENUM EQ 1.
.  COMPUTE #12hrs = TIME.HMS(12,0,0).
END IF.


*  ...  Compute "24-hour time" (that is, simply, time)          ... .
*       from the 12-hour time and AM/PM field                   ... .

*       Method A: Straightforward, but verbose                  ... .

DO IF    UPCASE(@AmPm) EQ 'AM'.
.  DO IF @Time12 LT #12hrs.
.     COMPUTE Time = @Time12.
.  ELSE.
.     COMPUTE Time = @Time12 - #12hrs.
.  END IF.
ELSE IF  UPCASE(@AmPm) EQ 'PM'.
.  DO IF @Time12 LT #12hrs.
.     COMPUTE Time = @Time12 + #12hrs.
.  ELSE.
.     COMPUTE Time = @Time12.
.  END IF.
END IF.


*       Method B: A single COMPUTE statement                    ... .
*       (WARNING: Will not detect errors in AM/PM field.)       ... .

COMPUTE TimeB = MOD(@Time12,#12hrs) + #12hrs*(@AmPm EQ 'PM').

*  ...  Recalculate the date-time value that was converted      ... .
*       to string "DT"                                          ... .

COMPUTE InstRcvr = Date + Time.

EXECUTE       /* to populate the active dataset */.
DATASET NAME     To24hrs  WINDOW = FRONT.


*  ...  Error-checking: Make sure the two methods of            ... .
*       calculating 24-hour time give the same results          ... .

TEMPORARY.
SELECT IF       Time    NE TimeB.

LIST /VARIABLES=CaseID DT
                 @Time12  @AmPm
                 Time     TimeB.


*  ................................................................ .
*  ...   Part IV:   Compare original and recovered date-time    ... .
*        values                                                 ... .

MATCH FILES
   /FILE=To12hrs
   /FILE=To24hrs
        /RENAME=(DT  @Time12  @AmPm
                =DTx Time12x  AmPmX)
   /BY   CaseID
   /DROP=@Date @Time @Time12  @AmPm
                     @Time12b @AmPmB
                     @Time12c @AmPmC
                 DTx Time12x  AmPmX  TimeB.

TEMPORARY.
SELECT IF Instant NE InstRcvr.
LIST.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD