I have an excel 2003 worksheet with time values in the following format:
hh:mm:ss

I'd like to look at my data in half hour chunks, rounded down to the nearest
half hour. Is there a formula I can use or a way to set up a pivot table to
allow me to do this?

For example I'd like the values in the left of the following list to be
converted to the values on the right:

02:13:42 02:00:00
02:23:33 02:00:00
02:28:55 02:00:00
02:43:18 02:30:00
02:53:35 02:30:00
03:12:34 03:00:00
03:18:41 03:00:00
03:53:41 03:30:00


Also, I think something odd is happening with my source data. My source
uses 12 hour time, but for some reason the conversion routine is pulling data
across in 24 hour time (but thats another issue, not one for here!) . This
means day 1 will show 8am, but the data for day 2 will show as occuring at
8pm, but also on day 1. Is there an easy way to change 24 hour time to 12
hour time in excel?

Thanks very much in advance for any help!

Kat

Hi Kat,

To round time down to the nearest half hour, you can use the following formula:

Assuming your time values are in column A, you can enter this formula in cell B1 and drag it down to apply it to all the cells in column B. This formula uses the HOUR function to extract the hour value from the time, the FLOOR function to round down the minute value to the nearest half hour, and the TIME function to combine the hour and minute values with 0 seconds.

To set up a pivot table to group your data by half hour chunks, you can follow these steps:

1. Select your data range, including the column headers.
2. Go to the "Insert" tab and click "PivotTable".
3. In the "Create PivotTable" dialog box, make sure the "Select a table or range" option is selected and that your data range is correctly displayed.
4. Choose where you want to place your pivot table and click "OK".
5. In the "PivotTable Fields" pane, drag the column containing your time values to the "Rows" area.
6. Right-click on any of the time values in the pivot table and select "Group".
7. In the "Grouping" dialog box, select "By" and choose "Number of Days", "Number of Months", or "Number of Years", depending on how you want to group your data.
8. In the "By" field, enter "0:30" to group your data by half hour chunks.
9. Click "OK" to apply the grouping.

To change 24 hour time to 12 hour time in Excel, you can use the following formula:

Assuming your 24 hour time values are in column A, you can enter this formula in cell B1 and drag it down to apply it to all the cells in column B. This formula uses the TEXT function to convert the time value to a text string in the format "h:mm AM/PM", where "h" represents the hour in 12 hour format, "mm" represents the minute, and "AM/PM" represents the time of day.

=TIME(HOUR(A1),FLOOR(MINUTE(A1),30),0)
and custom format to [h]:mm:ss

If this post helps click Yes
---------------
Jacob Skaria


"KatJ" wrote:

I have an excel 2003 worksheet with time values in the following format:
hh:mm:ss

I'd like to look at my data in half hour chunks, rounded down to the nearest
half hour. Is there a formula I can use or a way to set up a pivot table to
allow me to do this?

For example I'd like the values in the left of the following list to be
converted to the values on the right:

02:13:42 02:00:00
02:23:33 02:00:00
02:28:55 02:00:00
02:43:18 02:30:00
02:53:35 02:30:00
03:12:34 03:00:00
03:18:41 03:00:00
03:53:41 03:30:00


Also, I think something odd is happening with my source data. My source
uses 12 hour time, but for some reason the conversion routine is pulling data
across in 24 hour time (but thats another issue, not one for here!) . This
means day 1 will show 8am, but the data for day 2 will show as occuring at
8pm, but also on day 1. Is there an easy way to change 24 hour time to 12
hour time in excel?

Thanks very much in advance for any help!

Kat

=FLOOR(A1,1/48)

format cell: [hh]:mm:ss


"KatJ" wrote:

I have an excel 2003 worksheet with time values in the following format:
hh:mm:ss

I'd like to look at my data in half hour chunks, rounded down to the nearest
half hour. Is there a formula I can use or a way to set up a pivot table to
allow me to do this?

For example I'd like the values in the left of the following list to be
converted to the values on the right:

02:13:42 02:00:00
02:23:33 02:00:00
02:28:55 02:00:00
02:43:18 02:30:00
02:53:35 02:30:00
03:12:34 03:00:00
03:18:41 03:00:00
03:53:41 03:30:00


Also, I think something odd is happening with my source data. My source
uses 12 hour time, but for some reason the conversion routine is pulling data
across in 24 hour time (but thats another issue, not one for here!) . This
means day 1 will show 8am, but the data for day 2 will show as occuring at
8pm, but also on day 1. Is there an easy way to change 24 hour time to 12
hour time in excel?

Thanks very much in advance for any help!

Kat

Thanks for both responses - problem solved with either option (showing that
the half an hour leading up to break times is not surprisingly the peak time
for errors).

Much appreciated!

"KatJ" wrote:

I have an excel 2003 worksheet with time values in the following format:
hh:mm:ss

I'd like to look at my data in half hour chunks, rounded down to the nearest
half hour. Is there a formula I can use or a way to set up a pivot table to
allow me to do this?

For example I'd like the values in the left of the following list to be
converted to the values on the right:

02:13:42 02:00:00
02:23:33 02:00:00
02:28:55 02:00:00
02:43:18 02:30:00
02:53:35 02:30:00
03:12:34 03:00:00
03:18:41 03:00:00
03:53:41 03:30:00


Also, I think something odd is happening with my source data. My source
uses 12 hour time, but for some reason the conversion routine is pulling data
across in 24 hour time (but thats another issue, not one for here!) . This
means day 1 will show 8am, but the data for day 2 will show as occuring at
8pm, but also on day 1. Is there an easy way to change 24 hour time to 12
hour time in excel?

Thanks very much in advance for any help!

Kat