Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
To convert time to half hour interval, use the following formula:
Formula:
Let me break down the formula for you:
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() =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. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I convert time to the nearest 1/4 hour with a 7 min. window | Excel Worksheet Functions | |||
How to I convert standard time to Military or 24 hour format? | Excel Discussion (Misc queries) | |||
Convert Date/Time to the Hour | Excel Discussion (Misc queries) | |||
Calculate time difference to the half hour | Excel Worksheet Functions | |||
Convert decimal hour into time format? | Excel Discussion (Misc queries) |