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

[Dr. Darrell]

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

[Roger Govier]

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

[Alan]

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

[Bob Phillips]

=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

[a7n9]

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

[Bob Tarburton]

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

[Bob Phillips]

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

[Bob Tarburton]

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

[Roger Govier]

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

[Bob Phillips]

Geez that is confusing!

Bob


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>

[Roger Govier]

C'est la vie!!!

--
Regards

Roger Govier


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Geez that is confusing!
>
> Bob
>
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> >>> >
>> >>> >
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Tarburton]

I should get off this one, but...
note that the latest function always goes to the nearest top of the hour
unless A1 is exactly the bottom of the hour, or
=IF(MINUTE(A1)=30,A1,ROUND(A1*24,0)/24)

Maybe
=IF(AND(MINUTE(A1)<=15,MINUTE(A1)>=45),ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
??

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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
>>>> >
>>>> >
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Roger Govier]

Hi Bob

No, I don't think that is what the OP wanted.
His request
Data 6:00 12:00 12:45 17:15
Result 6:00 12:00 13:00 17:00
He didn't supply times for 6:30 and 12:30 but he did say the rounding
should be to the 30 minutes, other than 0:15 goes down and 0:45 goes up.

My first posting
=IF(MINUTE(A1)<30,FLOOR(A1,TIME(0,30,0)),CEILING(A1,TIME(1,0,0)))
Result 6:00 12:00 13:00 17:00 7:00 13:00
both these last 2 (which I didn't test), would have been wrong.

Bob's =ROUNDDOWN(A1*48,0)/48
Result 6:00 12:00 12:30 17:00 6:30 12:30
the first 12:30 is wrong

My amendment of Bob
=IF(MINUTE(A1)<30,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
Result 6:00 12:00 13:00 17:00 6:30 12:30
all as requested

Your latest proposal =IF(AND(MINUTE(A1)<=15,MINUTE(A1)>=45)
,ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
Result 6:00 12:00 13:00 17:30 6:30 12:30
the 17:30 time is wrong

I think on balance, Bob's formula, with my amendment just shades it<bg>

--
Regards

Roger Govier


"Bob Tarburton" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
>I should get off this one, but...
> note that the latest function always goes to the nearest top of the
> hour unless A1 is exactly the bottom of the hour, or
> =IF(MINUTE(A1)=30,A1,ROUND(A1*24,0)/24)
>
> Maybe
> =IF(AND(MINUTE(A1)<=15,MINUTE(A1)>=45),ROUNDDOWN(A1*48,0)/48,ROUNDUP(A1*48,0)/48)
> ??
>
> "Roger Govier" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>>>> >
>>>>> >
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>