Round down to the nearest day

A hospital administrator collect dates and times of needlestick injuries. The administrator wants to round the date/time column to the day, so that the time components are set to 0.

  1. Open the sample data, NeedleSticks.MTW.
  2. Choose Data > Date/Time > Round Date/Time.
  3. In Round values from this column, enter Date and time of needlestick.
  4. In Store the rounded values in, type Date.
  5. From Round off units smaller than, select Day.
  6. From Method, select Round down.
  7. Click OK.

Results

The original date/time values are in C1. The rounded values are in C2.

C1-D C2-D
Date and time of needlestick Date
1/2 12:00 1/2 0:00
1/10 3:10 1/10 0:00
1/17 11:33 1/17 0:00
... ...
Tip

To hide the time components, select and right-click the Date column and choose Format Column. Then, select a date display format without a time.

Round times to the nearest minute

To round a date/time column to the nearest minute, complete the following steps.

  1. In a column name cell, type Hrs Mins Secs.
  2. Copy and paste, or type the following data into the Hrs Mins Secs column:
    12:52:34
    04:32:10
    06:52:03
    11:22:33
  3. Choose Data > Date/Time > Round Date/Time.
  4. In Round values from this column, enter Hrs Mins Secs.
  5. In Store the rounded values in, type Hrs Mins.
  6. From Round off units smaller than, select Minute.
  7. From Method, select Round to nearest.
  8. Click OK.

Results

The original time values are in C1. The rounded time values are in C2.

C1-D C2-D
Hrs Mins Secs Hrs Mins
12:52:34 12:53:00
04:32:10 04:32:00
06:52:03 06:52:00
11:22:33 11:23:00

Round a date/time column to include only quarters and years

A manager collects date and time data and wants to create a bar chart where each bar shows the data for a quarter. To create the categorical variable Quarter, the manager rounds the date and time data to include only quarter and year. The manager then changes the column format to display only quarter and year.

  1. In a column name cell, type Date and Time.
  2. Copy and paste (select Paste as a single column when prompted), or type the following data into the Date and Time column:
    04/29/2014 12:52:34
    08/09/2015 04:32:10
    12/04/2014 06:52:03
    10/04/2013 11:22:33
  3. Choose Data > Date/Time > Round Date/Time.
  4. In Round values from this column, enter Date and Time.
  5. In Store the rounded values in, type Quarter.
  6. From Round off units smaller than, select Quarter.
  7. From Method, select Round down.
  8. Click OK.
  9. Click in the Quarter column.
  10. Choose Editor > Format Column.
  11. Under Custom date format, type qqyyyy.
  12. Click OK.

Results

The original time values are in C1. The rounded date values are in C2.

C1-D C2-D
Date and Time Quarter
04/29/2014 12:52:34 2Q2014
08/09/2015 04:32:10 3Q2015
12/04/2014 06:52:03 4Q2014
10/04/2013 11:22:33 4Q2013
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy