If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Convert time to Half Hour inverval
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Convert time to Half Hour inverval

 Thread Tools Display Modes
#1
March 29th 07, 05:06 PM 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
in advance for any assistance.
#2
March 29th 07, 05:18 PM 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
> in advance for any assistance.

#3
March 29th 07, 06:08 PM 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
> > in advance for any assistance.

>
>
>

#4
March 29th 07, 08:27 PM posted to microsoft.public.excel.misc
 Sandy Mann external usenet poster Posts: 2,348
Convert time to Half Hour inverval

 Thread Tools Display Modes Hybrid Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post How do I convert time to the nearest 1/4 hour with a 7 min. window Bigbus Excel Worksheet Functions 4 August 28th 06 03:09 PM How to I convert standard time to Military or 24 hour format? Nacho Excel Discussion (Misc queries) 5 June 28th 06 07:14 PM Convert Date/Time to the Hour eric_rs1 Excel Discussion (Misc queries) 2 October 3rd 05 04:40 PM Calculate time difference to the half hour Ken Ivins Excel Worksheet Functions 6 July 17th 05 05:48 PM Convert decimal hour into time format? ramdalen Excel Discussion (Misc queries) 2 June 20th 05 06:21 PM

All times are GMT +1. The time now is 05:48 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top