SPSS Date Calculations – A Quick Tutorial

Introduction & Practice Data File

SPSS date calculations are pretty straightforward. Just make sure you understand a handful of basics and keep it clean and simple. This tutorial shows you how to do just that. We'll use hospital.sav -shown below- throughout.

SPSS Main Date Functions

The table below shows SPSS’ main date functions. We'll show how to use them on a couple of examples below.

SPSS DATEDIFF Function

SPSS DATEDIFF returns the number of time units
(such as hours, days or years) between two date values. For instance, how many days ago did our respondents enter the hospital? We'll answer that by subtracting entry_date from the current date. The syntax below does just that.

Result

Simple as that. Just one thing to keep in mind is that DATEDIFF truncates (rounds down) its outcome values. So 2 years and 363 days are returned as 2 years and 0 days if years are chosen as the time unit.
If you don't want that, extract the number of days between 2 dates and divide them by 365.25 like we did in How to Compute Age in SPSS?

SPSS DATESUM Function

SPSS DATESUM adds a number of time units to a date variable. For subtracting time, just enter a negative value.

So say I want to contact respondents 3 months after they entered the hospital. However, 7 days before contacting them, they should be sent a notification. The syntax below shows how to add both dates to our data.

Result

SPSS XDATE Function

SPSS XDATE extracts a date component
(such as an hour, day or year) from a date. For example, the syntax below first extracts the year from entry_date and then the month.

Result

Again, it's as simple as that. An important warning, however, is that XDATE.WEEK returns nonsensical week numbers as discussed in SPSS Computes Wrong Week Numbers? Unfortunately, there's no easy way to extract the ISO week numbers that you probably want. We built an SPSS-Python tool for it but it somehow stopped working around SPSS version 24.

Another thing to keep in mind is that there's no such thing as XDATE.DAY. Instead, use

• XDATE.MDAY for the day of the month (1 through 31);
• XDATE.WKDAY for the day of the week (1 through 7 where 1 is Sunday, not Monday);
• XDATE.JDAY for the day of the year (1 through 366).

SPSS DATE.DMY Function

SPSS DATE.DMY creates a date from its components
such as day, month and year. So say I want to know how many days before 20 January 2015 patients entered the hospital. I'll just create a new date variable (or -rather- constant) holding this date and subtract it from entry_date.

Result

Obviously, DMY means “day, month, year” so that's the order in which we'll enter these date components. Now, in first instance, DATE.DMY results in a variable holding huge numbers. These are the numbers of seconds between the year 1582 and my actual data as explained in SPSS Date Variables Basics.
These huge numbers can be shown as normal dates by setting the appropriate formats. For instance, FORMATS today (DATE11). shows a date as “3-Sep-2018”. This is what we recommend because it's unambiguous which date this is. Please avoid showing dates like “01-02-03” because this could mean

• 1-Feb-2003 if its format is EDATE8 (European date),
• 2-Jan-2003 if its format is ADATE8 (American date) and
• 3-Feb-2001 if its format is SDATE8 (sortable date).

SPSS Date Comparisons

When we compare two numbers, we can simply ask if one number is larger than the other. The exact same, simple logic holds for date comparisons. However, there's one caveat: although we see “normal dates”, the underlying values (numbers of seconds since the year 1582) are used in date comparisons.
This may sound daunting but the solution is simple: if we want to compare an SPSS date value with some comparison date, we simply convert the comparison date into an actual SPSS date. We just saw how to do so easily: fill in the date components into DATE.DMY. We'll demonstrate this with some examples.
Say the hospital got a new CEO on February 20, 2014. We want to know if visits before this date are rated the same as visits after this date. We'll now select visits on and after February 20, 2014.

SPSS Date Comparison Example I

Result

Flagging visits that started on or after February 20, 2014.

SPSS Date Comparison Syntax Example II

The Summer holidays in 2014 were from June 30, 2014 through August 24, 2014. How can we select visits during these holidays? The syntax below shows how to do so easily by using DATE.DMY within RANGE.

That's basically it for the main DATE calculations in SPSS. We could come up with a million more examples but you'll probably figure them out yourself pretty easily. We hope.

Thanks for reading!

SPSS Computes Wrong Week Numbers?

• Wrong Week Numbers - Quick Demo
• SPSS Week Numbers Syntax Example
• ISO weeks in GoogleDocs
• Solution

Wrong Week Numbers - Quick Demo

While working on data holding a record for each day, I wanted to create some graphs on week level. So I extracted the weeks with XDATE.WEEK but the week numbers returned by SPSS are nonsensical: every week starts on January 1 and most years end up with week 53 holding just 1 day.

There's different standards for week numbers but I think the very definition of a week is a 7 day time span. The following syntax demonstrates the problem.

SPSS Week Numbers Syntax Example

The result in data view may look normal at first. However, when we scroll down to case 365, we see that week 53 consists of 1 day. Like so, SPSS’ week numbers don't correspond to any conventional standard and can neither be converted into one.

ISO weeks in GoogleDocs

Interestingly, Google sheets has the isoweeknum function returning the ISO weeks I'm looking for. So a “workable solution” seemed to copy-paste these into an SPSS data file. Finally, MATCH FILES by date seemed to do the trick. And then I realized...
In the ISO week system, dates around new year’s can fall into a week from a different year. And unfortunately, GoogleDocs does not provide the years to which weeks belong. The screenshot below attempts to illustrate the problem.

Right. So extracting the year from December 30, 2013 obviously returns 2013. However, it falls in week 1, 2014. And neither SPSS nor GoogleDocs offers a function that'll insert 2014 into my dataset for this date.

Solution

Perhaps a bit of an anti climax but... no solution so far. I could go and look for a huge table holding a long date range and all ISO weeks plus the years in which they fall. And convert it to SPSS. And merge it into several data files. But I'd much rather avoid such an ugly solution.
So... any suggestions anybody? Please drop me a comment below if you've a better idea.

Thanks for reading!

Convert String Date to SPSS Date Variable

“I've a string variable in my data holding dates formatted as ‘01JAN2016’ without separators between the day, month and year components. To make things worse, the month abbreviations are in Dutch and 3 of those differ from their English counterparts. How can I change this string into an SPSS date variable?”

Step 1: Add Separators

The data are in stringdates.sav. Now, generally, ALTER TYPE is the way to go for this job but the day-month-year separators missing poses a problem here. The solution is to add those by combining CONCAT with CHAR.SUBSTR . We'll do so in a new string variable because this allows us to inspect if the conversion succeeded.

Syntax 1

Step 2: Inspect Results

A huge flaw in ALTER TYPE is that it may result in system missing values without throwing any error or warning if it can't convert one or more values. Failing to detect this -not unlikely in larger datasets- may result in severely biased results. We'll therefore flag cases -if any- which have a system missing value on our new date variable but not an empty string value on our input variable.

Syntax 2

Result

Step 3: Replace Some Month Abbreviations

Note that we flagged some conversion failures but one case with an empty string value on our input variable is not one of them. The Dutch month abbreviations (such as “MRT” instead of “MAR”) are the reason for this. Fortunately, only 3 month abbreviations differ between English and Dutch.
We'll now convert our outcome variable back to string and recompute it. After doing so, we'll REPLACE the three deviant abbreviations in a DO REPEAT loop. After doing just that, we'll successfully convert our new variable into a date variable.

Syntax 3

Result

As we readily see, our conversion has now fully succeeded. In a larger dataset, you might want to inspect the result more carefully by flagging conversion failures like we did previously.

We really enjoyed writing this little tutorial. It nicely shows how combining the right building blocks gets a seemingly complicated job done with minimal effort and perfect precision. Hope you liked it too!

SPSS Time Variables Tutorial

Having a solid understanding of what SPSS time variables are, you may find calculations on them surprisingly easy. This tutorial will demonstrate SPSS' main time functions. However, we'll also show that we often don't even need them for getting things done.
Throughout this tutorial, keep in mind that SPSS time variables contain time spans in numbers of seconds that may or may not express clock times. Second, time variables are numeric variables so all numeric functions can be applied to them.
We encourage you try the time calculations we'll demonstrate yourself. You can do so by downloading and opening clock_card.sav.

SPSS Main Time Functions

Most of SPSS' date functions are intended for time variables as well. After outlining them in the table below, we'll take a closer look at them in the remainder of this tutorial.

SPSS DATEDIFF Function

Our data contain the entry and exit times of an employee as registered with a clock card. We first want to know how much time he spent in the office per day. The syntax below shows how to do so with and without using DATEDIFF. The screenshots show the results of both options.

SPSS DATEDIFF Syntax Example

SPSS DATESUM Function

Employees are supposed to spend 8 hours per day in the office. That is, their entry times should be their exit times minus 8 hours. Such time subtractions (or additions) are easily accomplished by using DATESUM. However, realizing that hours consist of 3600 seconds, we may obtain the same result with an ordinary addition as shown in the second example.

SPSS DATESUM Syntax Example

SPSS XDATE Function

Employees are supposed to be in before 10 AM. One way to flag late entries is to extract the hours from the entry times with XDATE. XDATE needs to be suffixed with the time unit we wish to extract as in XDATE.HOURS. Finally, we'll RECODE the hours into our flag variable.

SPSS XDATE Syntax Example

SPSS TIME.HMS Function

SPSS time variables hold numbers of seconds. TIME.HMS converts a number of hours, minutes and seconds into seconds and is thus creates SPSS time values from normal time components.
The minutes and seconds are optional; if omitted, they'll default to zero. That is, TIME.HMS(10) is a shorthand for TIME.HMS(10,0,0) and returns 36,000 (seconds). We can show this value as 10:00:00 by setting its format to TIME8.
The syntax below uses TIME.HMS as an alternative way to flag late entries.

SPSS TIME.HMS Syntax Example

SPSS Time Comparisons

SPSS time comparisons are utterly simple when we realize that SPSS time values are just numbers of seconds that are shown as hours, minutes and seconds. For comparing an SPSS time value to a normal time value (hours, minutes and seconds), simply convert the latter into seconds. TIME.HMS does just that. Next, simply use SPSS' standard operators such as >, <= and others.
For example, employees are not supposed to leave before 4 PM. The syntax below shows a super shorthand for flagging early exits.The unusual COMPUTE command is explained in Compute A = B = C.

SPSS Time Comparison Syntax Example 1

SPSS Time Comparison Example 2

Because TIME.HMS is a function, it can be substituted in other functions, particularly RANGE. The following example shows how to use it for flagging entries during rush hours (from 8 until 9 AM).

SPSS Time Variables in AGGREGATE

This final example again reemphasizes that SPSS time variables are numeric variables, holding seconds, on which normal numeric functions can be used.
For example, employees are supposed to work 40 hours per week. To what extent do our data meet that criterion? We already calculated duration_time, which is a time variable holding seconds. We can simply sum it per week by using AGGREGATE. This results in seconds per week which we'll show as normal times by setting their format to TIME8.

SPSS Datetime Variables Basics

Working with SPSS datetime variables is not hard at all if you understand some basics. This tutorial walks you through just those. Those who'd like to follow along may download and open hospital.sav.

SPSS Datetime Variables - What Are They?

SPSS datetime variables are variables that hold the numbers of seconds between the year 1582 and a given time on a given date. SPSS datetime values may look complicated (containing letters of months and dashes) but their values are really nothing more than huge numbers. The actual values are shown by specifying an f format, the syntax for which is formats exit_moment(f1).

Note that this doesn't change the values in any way; they're merely displayed differently. Don't let their unusual appearance fool you: SPSS datetime variables are numeric variables. This implies that all standard numeric functions can be used on them. However, for calculations on SPSS datetime variables we'll mostly use SPSS date functions.

SPSS Datetime Formats

We just saw that SPSS datetime values really are huge numbers of seconds. For displaying them as normal dates with times, set their format to one of the two formats outlined below.

Date, Time and Datetime

The relation between SPSS date variables, time variables and datetime variables can be seen from a quick comparison of their definitions:

• SPSS date variables contain the number of seconds between 1582 and the very start (midnight) of a given date;
• SPSS time variables contain the number of seconds between the very start (midnight) of a date and some given time;
• SPSS datetime variables contain the number of seconds between 1582 and a given time on a given date.

We conclude from this that SPSS date values can be seen as datetime values with a 00:00:00 time component. Running formats entry_date(datetime20). confirms this; SPSS date values can be properly displayed as datetime20 because their actual values are very similar to datetime values.

Reversely, datetime values can be displayed as dates as well. If doing so, just keep in mind that the time component does not disappear by no longer displaying it.

SPSS Datetime from Date and Time

At this point we may see that SPSS datetime values are simply the sum of a date value and a time value. Running the syntax below confirms this.

Combining date and time into datetime - it really that simple.

SPSS Extract Date from Datetime

SPSS users who understand datetime variables will rarely -if ever- want to extract their date components. For the sake of completeness, the official way is to create the date variable using DATE.DMY. We obtain the required day, month and year components by applying XDATE to the datetime variable.
Note the day of the month is captured by XDATE.MDAY; XDATE.DAY is not valid in SPSS.In the syntax below, we first create day, month and year as intermediate variables before combining them with DATE.DMY. This step may be skipped by substituting XDATE into DATE.DMY which we'll demonstrate when extracting time values from datetime values.

The unofficial way to extract date values from datetime values uses SPSS TRUNC function; we remove the time portion from datetime values by rounding them down to 86400 seconds (one day).

SPSS Extract Time from Datetime

SPSS time values can be created from hours, minutes and seconds by TIME.HMS. Again, these components can be extracted from datetime values by using XDATE as shown in the syntax below.

A faster alternative here is using SPSS MOD function; we basically throw away the date component by removing all 86400-folds (a day has 86400 seconds) from the datetime values.

SPSS Datetime Variables Tutorial

This tutorial shows how to work proficiently with SPSS datetime variables. You can follow along with it by downloading and opening hospital.sav.

SPSS Main Datetime Functions

This tutorial will cover the datetime functions outlined in the table below. Most of them apply to SPSS date variables and time variables as well because their values are stored in numbers of seconds too.

SPSS Date and Time to Datetime

We'll first combine entry_date and entry_time into entry_moment because we'll need it a bit later on. The syntax below shows how to do so with a very basic COMPUTE command followed by FORMATS.

SPSS DATESUM Function

SPSS DATESUM adds to datetime variables a given number of time units (days, hours and so on). Specify a negative number of time units for subtraction. For example, the hospital staff wants to contact their patients for a survey exactly 1 month after they've left the hospital. A notification should be sent 7 days prior to contacting patients.
Both datetime variables are easily created with SPSS DATESUM as shown in the syntax below. The result in data view is shown in the following screenshot.

SPSS DATEDIFF Function

SPSS DATEDIFF function returns the difference between two datetime values in a given time unit (hours, days and so on). For example, how long did the patients stay in the hospital? The syntax below first answers the question by using DATEDIFF.
Now, keep in mind that DATEDIFF truncates (rounds down) its return values. Personally, we prefer using a basic subtraction here. Because datetime values are numbers of seconds, the result is a number of seconds too. However, we can easily show these as hours, minutes and seconds by giving it a time format. This is shown in the second example below.

SPSS DATEDIFF Syntax Example

SPSS CTIME Function

SPSS CTIME converts seconds to other time units such as hours, days or months.Oddly, CTIME.YEARS is painfully missing in SPSS while CTIME.SECONDS -which does basically nothing- is present instead. In contrast to DATEDIFF, return values aren't truncated.
Note that duration_time is a time variable so it really holds numbers of seconds. We can convert those to the desired time units with CTIME as demonstrated below; the syntax recalculates duration in days but this time without truncating the outcome values.
An alternative to CTIME here is using a basic division; since a day holds 86,400 seconds, dividing time values by 86400 is equivalent to using CTIME.DAYS. This is shown in the second example below.

SPSS CTIME Syntax Example

SPSS XDATE Function

SPSS XDATE extracts date components from date, time and datetime values. The syntax below thus shows how to extract the day, month and year from exit_moment. Note that XDATE.MDAY rather than XDATE.DAY returns the day of the month.

SPSS Datetime Comparisons

Comparing two SPSS datetime variables is straightforward and can be done with the usual operators such as >, <= and others. For example, exit_moment must obviously be greater (later in time) than entry_moment for all visits. The syntax confirms that this holds for all visits by using IF.

SPSS Datetime Comparison Example 1

As we mentioned before, datetime values are numbers of seconds. You can compare datetime values to a given date by converting the latter into seconds as well. This is readily done by DATE.DMY as we'll show in a minute.
For example, a report criticizing the hospital was published on November 2nd., 2014. As a first step in evaluating its impact on patient ratings, we'll flag all visits that ended on or after this date.The COMPUTE command used in the syntax below looks a bit weird. It is explained in Compute A = B = C. Keep in mind that date values are identical to datetime values with 00:00:00 as their time components.

SPSS Datetime Comparison Example 2

The report was published at 12:10:05 (10 minutes, 5 seconds past noon). Note that two visits ending on the publication date but before the publication time were flagged. How can we exclude such cases?
Well, remember that an SPSS datetime value is identical to the sum of an SPSS date value and an SPSS time value. The syntax below uses this in order to compare a datetime variable to a given date and time.

SPSS Datetime Comparison Example 3

SPSS – Convert String into Date Variable

For converting string variables to date variables, ALTER TYPE is the way to go. This tutorial demonstrates how to do this and points out a couple of caveats. For those who can't use ALTER TYPE, we'll propose some alternatives.

SPSS Alter Type

The syntax below first creates a mini dataset holding 8 string variables. They demonstrate the 8 most common date formats. Next, each string variable is converted to a date variable by using ALTER TYPE.

SPSS String to Date Syntax

String to Date Syntax Notes

• The date format in ALTER TYPE tells SPSS which component is the year, month and day. So if 1/2/3 means 1 February 2003, use EDATE8 (dd-mm-yy). If it means January 2nd, 2003, use ADATE8 (mm-dd-yy) and so on.
• In the original string variable, the year, month and day may be separated by a dash (-), a slash (/), a period (.) or even a combination of these. It doesn't matter which one is used.
• If two digits are used for years, numbers 0 through 44 are interpreted as years 2000 through 2044. Numbers 45 through 99 are interpreted as 1945 through 1999. If that's not to your liking, use SET EPOCH...
• ALTER TYPE overwrites existing values and is not reversible. It's usually no big problem if things go wrong here as long as you stick to sound practices such as working from syntax. Additionally, you can use SPSS Clone Variables Tool before ALTER TYPE.

SPSS String to Date without Alter Type

Note that ALTER TYPE can only be used for a limited number of date formats. Some more exotic formats may require a more flexible approach. Second, those on SPSS versions 15 and below don't have ALTER TYPE since it was introduced in version 16.

For both scenarios, we'll usually extract the year, month and day by using SUBSTR, often combined with INDEX and RINDEX. We'll then convert these into an SPSS date variable by using the DATE.DMY function. Finally, we'll display the number of seconds it holds as a more readable date by using FORMATS.

SPSS String to Date Syntax

Dealing with Months as Letters

The previous example converted the first string variable but the second is slightly harder. This is because DATE.DMY requires three numbers but months are now shown as letters (e.g. JAN, FEB and so on). An easy way to fix this is to REPLACE the months by numbers (00 through 12) using DO REPEAT as shown below. After doing so, one can proceed as in the previous example.

Extract a Year from a Date

Introduction

Extracting the year (or any component) from a date is straightforward with the XDATE ("EXtract DATE") function. Copy-paste-run the syntax presented below for a demonstration of XDATE.

SPSS Xdate Syntax Example

Notes

• The XDATE function can also be used for extracting the day or month from a date or the date portion from a DATETIME variable.
• The only thing to make sure is that the input variable is a proper date variable.

SPSS Time Variables Basics

Introduction

Working efficiently with SPSS time variables is not hard if you understand some basics. This tutorial walks you through just those. We recommend you follow along by downloading and opening hospital.sav.

SPSS Time Variables - What Are They?

SPSS time variables are variables that hold time intervals in numbers of seconds. Although the actual time values are just simple numbers, they are usually displayed as hours, minutes and seconds. Don't let this appearance confuse you; it's the underlying numbers of seconds that time calculations act upon.
Note that SPSS time variables are numeric variables: all of SPSS' numeric functions can be used on them in the exact same way as you would on other numeric variables. However, we often use SPSS time functions for calculations on time variables.

SPSS Common Time Formats

Time values are numbers of seconds but we usually display them as hours, minutes and seconds. We can do so by setting their format to one of SPSS' time formats. The table below shows the most common ones.

SPSS Time Variables - Example

We'll now focus on entry_time in our data. We'll take a look at its actual values (numbers of seconds) in data view; running the following line of syntax will show them: formats entry_time(f1).

Note that this doesn't change the values in any way; they're merely displayed differently. We can show them as normal time values again by running formats entry_time(time8). Knowing that time values are really just numbers of seconds makes it pretty easy to work with them. For instance, we can set all time values one hour ahead by simply adding 3600 (seconds in one hour) to them by running the syntax below.

SPSS Time Variable Syntax Example

Looking at the result, we notice something strange: the first couple of values exceed 24 hours. That is pretty odd in this particular case. However, it generally makes sense when we understand the nature of SPSS time values, which we'll discuss next.

SPSS Time Values

Keep in mind that SPSS time values contain time intervals. These often indicate clock times as intervals between midnight and a given time point. However, this is not necessarily the case.
For example, the duration of a hotel stay (difference between exit moment and arrival moment) will typically be entered as a time variable in SPSS. This time interval probably doesn't start at midnight and will often exceed 24 hours.
In short, SPSS time variables hold time spans that may or may not indicate clock times. SPSS time values may also exceed 24 hours because they don't contain a day or month component.

SPSS Date Variables Basics

SPSS date variables may seem a bit puzzling at first. However, getting things done fast and accurately with SPSS date variables is not hard at all if you understand some basics. This tutorial will walk you through. You can follow along by downloading and opening hospital.sav.

SPSS Date Variables - What Are They?

First of all, SPSS date variables are numeric variables; their actual values are just numbers. More precisely, the numbers they hold are the number of seconds between the year 1582 and the start (midnight) of a given date. Realizing that it's these underlying values that date calculations act upon renders SPSS date logic straightforward.
Second, these huge numbers are usually shown as normal dates by setting their format to one of SPSS' main date formats. We'll outline some of those in the table below.

SPSS Date Formats

As we just mentioned, SPSS date values are always numbers of seconds. You can choose several formats for displaying them as actual dates. Some main options are given in the table below.

As suggested by this table, we recommend that you always display years as 4 digits instead of 2.

SPSS Date Variables - Example

The aforementioned points may sound rather abstract so let's demonstrate them on our data. It contains one proper date variable, entry_date. We'll take a look at its actual values by giving it an f format. Running the following line of syntax does just that. formats entry_date(f1).

The date values now look very differently in data view. However, they are still the exact same values that we had a few seconds ago; they're just displayed differently. We'll now show them as dates by running formats entry_date(sdate10). After doing so, the actual date values look like date values again. Again, keep in mind that actual values still haven't changed in any way.

SPSS Date Calculations

So why bother about the huge numbers that underlie SPSS date values? The reason is that its these values that SPSS date functions act upon.
When we understand that date values are really numbers of seconds, it becomes very easy to add, say, one day to date values. Note that one day is 60 (seconds) * 60 (minutes) * 24 (hours) = 86400 seconds. Now watch what happens when we simply add 86400 to our date variable by running the syntax below.

Indeed, exactly one day has been added to all values. However, adding a month is more complicated because different months hold different numbers of days.
For such cases SPSS has some specialized date functions that render such operations a piece of cake. The next tutorial will walk you through the most important ones.