Remember Me?

Posted to microsoft.public.excel.misc
 BenS external usenet poster Posts: 22 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
 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: Convert time to Half Hour inverval

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

Formula:
``` =TIME(HOUR(A1),FLOOR(MINUTE(A1)/30,1)*30,0)  ```
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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.misc
 George Nicholson external usenet poster Posts: 149 Convert time to Half Hour inverval

=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

Posted to microsoft.public.excel.misc
 BenS external usenet poster Posts: 22 Convert time to Half Hour inverval

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

Posted to microsoft.public.excel.misc
 Sandy Mann external usenet poster Posts: 2,345 Convert time to Half Hour inverval

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Bigbus Excel Worksheet Functions 4 August 28th 06 03:09 PM Nacho Excel Discussion (Misc queries) 5 June 28th 06 07:14 PM eric_rs1 Excel Discussion (Misc queries) 2 October 3rd 05 04:40 PM Ken Ivins Excel Worksheet Functions 6 July 17th 05 05:48 PM ramdalen Excel Discussion (Misc queries) 2 June 20th 05 06:21 PM

All times are GMT +1. The time now is 07:56 AM. Copyright ©2004-2023 ExcelBanter.