Round off the times in a column containing dates and times

You can round a date/time column to the day, so that the time components are set to 0.

C1-D
Date_and_Time
04/29/2014 12:52:34
08/09/2015 04:32:10
12/04/2014 06:52:03
10/04/2013 11:22:33
Original data
C2-D
Date
4/29/2014 00:00:00
8/09/2015 00:00:00
12/04/2014 00:00:00
10/04/2013 00:00:00
Rounded data
  1. Choose Data > Date/Time > Round Date/Time.
  2. In Round values from this column, enter Date_and_Time.
  3. In Store the rounded values in, enter Date.
  4. In Round off units smaller than, choose Day.
  5. In Method, choose Round down.
  6. Click OK.

Round times to the nearest minute

You can round a date/time column to the nearest minute, so that the seconds are set to 0.

C1-D
Hrs_Mins_Secs
12:52:34
04:32:10
06:52:03
11:22:33
Original data
C2-D
Hrs_Mins
12:53:00
04:32:00
06:52:00
11:23:00
Rounded data
  1. Choose Data > Date/Time > Round Date/Time.
  2. In Round values from this column, enter Hrs_Mins_Secs.
  3. In Store the rounded values in, enter Hrs_Mins.
  4. In Round off units smaller than, choose Minute.
  5. In Method, choose Round to nearest.
  6. Click OK.

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

You can round a column that contains a full date and time to include only quarter and year. This is useful, for example, if you want to use the quarter and year on the x-axis of a scatterplot or bar chart.

C1-D
Date_and_Time
04/29/2014 12:52:34
08/09/2015 04:32:10
12/04/2014 06:52:03
10/04/2013 11:22:33
Original data
C2-D
Quarter
2Q2014
3Q2015
4Q2014
4Q2013
Rounded data
  1. Choose Data > Date/Time > Round Date/Time.
  2. In Round values from this column, enter Date_and_Time.
  3. In Store the rounded values in, enter Quarter.
  4. In Round off units smaller than, choose Quarter.
  5. In Method, choose Round down.
  6. Click OK.
  7. Click in any cell in the Quarter column.
  8. Choose Editor > Format Column > Date/Time.
  9. In New format, enter qqyyyy.
  10. Click OK.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy