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

K

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
 
J

Jacob Skaria

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

If this post helps click Yes
 
K

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top