Convert time to Half Hour inverval

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.

=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.