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(A 1,TIME(1,0,0)))

--
Regards

Roger Govier


"Dr. Darrell" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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(D530,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

I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in message
...
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" wrote in message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in message
...
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" wrote in message
...
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" wrote in message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in message
...
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" wrote in message
...
Okay, now that I read the second half, I see that when to
roundup/rounddown was not specified.
My apologies, Bob

"Bob Phillips" wrote in message
...
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" wrote in
message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in
message
...
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

Geez that is confusing!

Bob


"Roger Govier" wrote in message
...
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" wrote in message
...
Okay, now that I read the second half, I see that when to
roundup/rounddown was not specified.
My apologies, Bob

"Bob Phillips" wrote in message
...
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" wrote in
message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in
message
...
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

C'est la vie!!!

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Geez that is confusing!

Bob


"Roger Govier" wrote in message
...
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" wrote in
message
...
Okay, now that I read the second half, I see that when to
roundup/rounddown was not specified.
My apologies, Bob

"Bob Phillips" wrote in message
...
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" wrote in
message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in
message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in
message
...
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 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(A 1*48,0)/48,ROUNDUP(A1*48,0)/48)
??

"Roger Govier" wrote in message
...
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" wrote in message
...
Okay, now that I read the second half, I see that when to
roundup/rounddown was not specified.
My apologies, Bob

"Bob Phillips" wrote in message
...
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" wrote in message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in message
...
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 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(A 1,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" wrote in message
...
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(A 1*48,0)/48,ROUNDUP(A1*48,0)/48)
??

"Roger Govier" wrote in message
...
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" wrote in
message ...
Okay, now that I read the second half, I see that when to
roundup/rounddown was not specified.
My apologies, Bob

"Bob Phillips" wrote in message
...
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" wrote in
message
...
I think Bob meant ROUNDUP

"Bob Phillips" wrote in
message
...
=ROUNDDOWN(A1*48,0)/48


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dr. Darrell" wrote in
message
...
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