How do I round time down to the nearest half hour?

[KatJ]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Teethless mama]

=FLOOR(A1,1/48)

format cell: [hh]:mm:ss

 

[KatJ]

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!