I have a list of date/time values which I plan to use text-to columns to
strip away the time.

Next, I need to convert these time values into half hour interval values but
don't know how to do this.

For example
Original Value Converted Value
23:16 23:00
09:56 09:30

So, you can see I'm converting the time to the last half hour that passed.
Can anyone suggest a formula or method to simplify this for me? Many thanks
in advance for any assistance.

To convert time to half hour interval, use the following formula:

Here, A1 is the cell containing the original time value.

Let me break down the formula for you:

1. HOUR(A1) extracts the hour value from the original time.
2. MINUTE(A1) extracts the minute value from the original time.
3. FLOOR(MINUTE(A1)/30,1)*30 rounds down the minute value to the nearest half hour. For example, if the original minute value is 16, this formula will round it down to 0. If the original minute value is 56, it will round it down to 30.
4. TIME(HOUR(A1),FLOOR(MINUTE(A1)/30,1)*30,0) combines the rounded down minute value with the original hour value to create the new time value.

To use this formula, simply replace A1 with the cell reference containing the original time value. Then copy the formula down to the rest of the cells in the column.

=TIME(HOUR(A2),IF(MINUTE(A2)<30,0,30),0)

Note that this will also strip away date values as well.

HTH,


"BenS" wrote in message
...
I have a list of date/time values which I plan to use text-to columns to
strip away the time.

Next, I need to convert these time values into half hour interval values
but
don't know how to do this.

For example
Original Value Converted Value
23:16 23:00
09:56 09:30

So, you can see I'm converting the time to the last half hour that passed.
Can anyone suggest a formula or method to simplify this for me? Many
thanks
in advance for any assistance.

George,

Thank you so much for your help. That was exactly what I needed and it has
saved me a great deal of time and effort. Have a fantastic day!

"George Nicholson" wrote:


=TIME(HOUR(A2),IF(MINUTE(A2)<30,0,30),0)

Note that this will also strip away date values as well.

HTH,


"BenS" wrote in message
...
I have a list of date/time values which I plan to use text-to columns to
strip away the time.

Next, I need to convert these time values into half hour interval values
but
don't know how to do this.

For example
Original Value Converted Value
23:16 23:00
09:56 09:30

So, you can see I'm converting the time to the last half hour that passed.
Can anyone suggest a formula or method to simplify this for me? Many
thanks
in advance for any assistance.

Just anoth option:

=FLOOR(A2,0.5/24)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"BenS" wrote in message
...
I have a list of date/time values which I plan to use text-to columns to
strip away the time.

Next, I need to convert these time values into half hour interval values
but
don't know how to do this.

For example
Original Value Converted Value
23:16 23:00
09:56 09:30

So, you can see I'm converting the time to the last half hour that passed.
Can anyone suggest a formula or method to simplify this for me? Many
thanks
in advance for any assistance.