... round a time entry to the next "30 minute increment"...

I have a worksheet that I enter time values into individual cells:

A1 = 6:00
B1 = 12:00
C1 = 12:45
D1 = 17:15

When I enter these values, I would like some of the values to round to the
next higher "30 minute increment" such as Cell C1 should become 13:00.

I would also like some of the values to round back to the next lower "30
minute increment" such as Cell D1 should become 17:00.

What function should I use, or what code should I type for this.

Darrell

Hi

One way
=IF(MINUTE(A1)<30,FLOOR(A1,TIME(0,30,0)),CEILING(A1,TIME(1,0,0)))

--
Regards

Roger Govier


"Dr. Darrell" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet that I enter time values into individual cells:
>
> A1 = 6:00
> B1 = 12:00
> C1 = 12:45
> D1 = 17:15
>
> When I enter these values, I would like some of the values to round to
> the
> next higher "30 minute increment" such as Cell C1 should become 13:00.
>
> I would also like some of the values to round back to the next lower
> "30
> minute increment" such as Cell D1 should become 17:00.
>
> What function should I use, or what code should I type for this.
>
> Darrell

Try
=ROUND(C1*24,0)
and format the cell as a number with two decimal places
This will display 13.00 as opposed to 13:00, is that a problem? If so post
back,
Regards,
Alan.
"Dr. Darrell" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet that I enter time values into individual cells:
>
> A1 = 6:00
> B1 = 12:00
> C1 = 12:45
> D1 = 17:15
>
> When I enter these values, I would like some of the values to round to the
> next higher "30 minute increment" such as Cell C1 should become 13:00.
>
> I would also like some of the values to round back to the next lower "30
> minute increment" such as Cell D1 should become 17:00.
>
> What function should I use, or what code should I type for this.
>
> Darrell

=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" <[email protected]> wrote in message
news:[email protected]...
> I have a worksheet that I enter time values into individual cells:
>
> A1 = 6:00
> B1 = 12:00
> C1 = 12:45
> D1 = 17:15
>
> When I enter these values, I would like some of the values to round to the
> next higher "30 minute increment" such as Cell C1 should become 13:00.
>
> I would also like some of the values to round back to the next lower "30
> minute increment" such as Cell D1 should become 17:00.
>
> What function should I use, or what code should I type for this.
>
> Darrell

I think Bob meant ROUNDUP

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =ROUNDDOWN(A1*48,0)/48
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Dr. Darrell" <[email protected]> wrote in message
> news:[email protected]...
>> I have a worksheet that I enter time values into individual cells:
>>
>> A1 = 6:00
>> B1 = 12:00
>> C1 = 12:45
>> D1 = 17:15
>>
>> When I enter these values, I would like some of the values to round to
>> the
>> next higher "30 minute increment" such as Cell C1 should become 13:00.
>>
>> I would also like some of the values to round back to the next lower "30
>> minute increment" such as Cell D1 should become 17:00.
>>
>> What function should I use, or what code should I type for this.
>>
>> Darrell
>
>

No, I meant ROUNDDOWN. The OP said ... I would also like some of the values
to round back to the next lower "30
.....

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
> I think Bob meant ROUNDUP
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =ROUNDDOWN(A1*48,0)/48
> >
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Dr. Darrell" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have a worksheet that I enter time values into individual cells:
> >>
> >> A1 = 6:00
> >> B1 = 12:00
> >> C1 = 12:45
> >> D1 = 17:15
> >>
> >> When I enter these values, I would like some of the values to round to
> >> the
> >> next higher "30 minute increment" such as Cell C1 should become 13:00.
> >>
> >> I would also like some of the values to round back to the next lower
"30
> >> minute increment" such as Cell D1 should become 17:00.
> >>
> >> What function should I use, or what code should I type for this.
> >>
> >> Darrell
> >
> >
>
>

Okay, now that I read the second half, I see that when to roundup/rounddown
was not specified.
My apologies, Bob

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> No, I meant ROUNDDOWN. The OP said ... I would also like some of the
> values
> to round back to the next lower "30
> ....
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
> news:[email protected]...
>> I think Bob meant ROUNDUP
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > =ROUNDDOWN(A1*48,0)/48
>> >
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from the email address if mailing direct)
>> >
>> > "Dr. Darrell" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I have a worksheet that I enter time values into individual cells:
>> >>
>> >> A1 = 6:00
>> >> B1 = 12:00
>> >> C1 = 12:45
>> >> D1 = 17:15
>> >>
>> >> When I enter these values, I would like some of the values to round to
>> >> the
>> >> next higher "30 minute increment" such as Cell C1 should become 13:00.
>> >>
>> >> I would also like some of the values to round back to the next lower
> "30
>> >> minute increment" such as Cell D1 should become 17:00.
>> >>
>> >> What function should I use, or what code should I type for this.
>> >>
>> >> Darrell
>> >
>> >
>>
>>
>
>

Aah! but it was.
The OP said round to the next higher half hour when 12:45, and round
back to 12:00 when it was 12:15
Maybe
=IF(MINUTE(A1)<30,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)


--
Regards

Roger Govier


"Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
> Okay, now that I read the second half, I see that when to
> roundup/rounddown was not specified.
> My apologies, Bob
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>> No, I meant ROUNDDOWN. The OP said ... I would also like some of the
>> values
>> to round back to the next lower "30
>> ....
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from the email address if mailing direct)
>>
>> "Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in
>> message
>> news:[email protected]...
>>> I think Bob meant ROUNDUP
>>>
>>> "Bob Phillips" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > =ROUNDDOWN(A1*48,0)/48
>>> >
>>> >
>>> > --
>>> >
>>> > HTH
>>> >
>>> > Bob Phillips
>>> >
>>> > (remove nothere from the email address if mailing direct)
>>> >
>>> > "Dr. Darrell" <[email protected]> wrote in
>>> > message
>>> > news:[email protected]...
>>> >> I have a worksheet that I enter time values into individual
>>> >> cells:
>>> >>
>>> >> A1 = 6:00
>>> >> B1 = 12:00
>>> >> C1 = 12:45
>>> >> D1 = 17:15
>>> >>
>>> >> When I enter these values, I would like some of the values to
>>> >> round to
>>> >> the
>>> >> next higher "30 minute increment" such as Cell C1 should become
>>> >> 13:00.
>>> >>
>>> >> I would also like some of the values to round back to the next
>>> >> lower
>> "30
>>> >> minute increment" such as Cell D1 should become 17:00.
>>> >>
>>> >> What function should I use, or what code should I type for this.
>>> >>
>>> >> Darrell
>>> >
>>> >
>>>
>>>
>>
>>
>
>

Although, this is not elegant it should get you started, suppose D5 has your
time.
=IF(MINUTE(D5)<30,TIME(HOUR(D5),0,0),IF(D5>30,TIME(HOUR(D5)+1,0,0),D5))

Actually, the functions CEILING and FLOOR could be used, but I can't figure
it right now.
--
-When you get to the end of your rope, tie a knot and hang on
<a href="www.nandeshwar.info/projects/xlblog">My Excel/VBA Page</a>


"Dr. Darrell" wrote:

> I have a worksheet that I enter time values into individual cells:
>
> A1 = 6:00
> B1 = 12:00
> C1 = 12:45
> D1 = 17:15
>
> When I enter these values, I would like some of the values to round to the
> next higher "30 minute increment" such as Cell C1 should become 13:00.
>
> I would also like some of the values to round back to the next lower "30
> minute increment" such as Cell D1 should become 17:00.
>
> What function should I use, or what code should I type for this.
>
> Darrell