Tips and Tricks for Date/Time Data
Whether you're a Lean Six Sigma black belt, a researcher, or a statistics student, at some point you will need to work with data that involve either dates, times, or both. Do you know where all of your date/time tools are? Let’s take a quick trip through Minitab Statistical Software and see what it has to offer for date/time data:
Column Indicators of Date and Time Data
You will know when Minitab recognizes a column as a date/time format if it has a “-D” to the right of the number, as in “C1-D”. If Minitab recognizes the column as text data, you’ll see a “C1-T.” If it’s numeric data, nothing will appear to the right of C1.
Changing a Column into Date/Time Format
Minitab tries to determine column formats when you paste data into a worksheet, but sometimes it will mistake a date column for text. You can fix this a number of ways:
You can change the data type prior to importing the data into Minitab. If the data comes from one of the file types listed under File > Open Worksheet, you can click on the “Preview” button located below “Files of type”, which will allow you to correct any misclassifications.
- If you've already imported your data, go to Data > Change Data Type. In this menu, you can select the format that matches what the data represents. If you want the data to be in a date/time format and Minitab currently treats it as text, select Text to Date/Time.
In this dialog window, you’ll be asked to specify what date/time format the data is in. Minitab Help includes a nice list of valid date/time components, which can be accessed by clicking on the Help button in the lower left corner of the dialog for Change Data Type > Text to Date/Time. Here is an excerpt from that list:
You’ll have to utilize these components to generate a format that Minitab understands. If the dates in your text column have values such as 2/2/2012, you would have to specify a format of 'm/d/yyyy' under Format of text columns in the dialog window.
Basic Tips for Formatting Date and Time Data
These are some tips that I have found very helpful in constructing date/time formats:
- Default date (not time) components can be separated by (/), (-), or (.). All components must be separated by the same symbol (except for the default format mmmm d, yy).
- Time components are separated as follows: hh:mm:ss AM/PM
- To indicate elapsed time, enclose the largest time unit in square brackets. If the format was set to [h]:mm:ss, Minitab would interpret 10:23 as 10 hours and 23 minutes. If the time format were [m]:ss, Minitab would interpret 10:23 as 10 minutes and 23 seconds.
- For combining dates and time in one format, the date part must always appear first, and there must be a space between the date portion and the time portion.
Manipulating an Existing Date/Time Format
Once you’ve established a date/time format for a column, you might wish to change it so that it only shows mmm/yyyy instead of mmm/dd/yyyy. To do this, left-click to select any cell in the date/time column, then right-click and go to Format Column > Date/Time.
You’ll see a list of available formats to select from, and you’ll also have the opportunity to create a new format by typing it under the “New Format” box. If you are concerned about losing the information in the column after selecting a new format, don’t worry. This feature only changes the visibility of components in the worksheet, and doesn’t actually remove any information about the date/time. So the value for the day will remain a part of your data.
Minitab offers additional tools for handling date/time, like extractions, calculations, and coding. I'll cover those in my next post! Meanwhile, I hope this post helps you the next time you are working with Minitab!